Tag Info

New answers tagged

1

Got to psql, issue \d your_table and look up the referenced table's name. If you set \set ECHO_HIDDEN on beforehand, you'll get a bunch of queries that produce the output, those you can reuse in your own discovery script. For example, on my test database these look like test=# \d tfk Table "test.tfk" Column │ Type │ Modifiers ...


0

About errors above, because of the connection between pgpool and slave server, if I change slave's pg_hba.conf for pgpool host from md5 to trust, it work fine. Two ways to fix: ssh config among: pgpool, slave, master . set up trust method in pg_hba.conf for pgpool .


2

This can only be if ... you did not actually commit your transaction (yet) and running the second query in a different transaction. or you have other transactions writing to the table in the meantime or something is seriously broken or you are dealing with two different tables: It's worth mentioning that your ALTER TABLE commands are on ...


0

It was a combination of making too many transactions and too many functions. After combining statements into the same transactions and removing the functions, the CPU hasn't risen past an average of 5%.


0

Summary: VARCHAR and TEXT are identical for reading, storing, FULLTEXT, and virtually everything else. Some INDEX/SELECT differences are noted below. MariaDB and MySQL are (I'm pretty sure) the same as each other in this area. Some reason for 765? What CHARACTER SET are you using? (Ascii versus utf8 -- there could be differences.) For "small" VARCHARs ...


1

As far as Postgres is concerned, there is no difference in performance. And there is no length modifier for text to begin with, just (optionally) for varchar. Unless you need to enforce a certain length, just use text or varchar. Would index lookup be noticeably faster with char vs varchar when all values are 36 chars To support pattern matching with ...


2

Simplified query to make it readable: SELECT * FROM "Follows" f JOIN "Users" u ON f."followeeId" = u."id" WHERE f."followerId" = 169368 ORDER BY f."createdAt" DESC LIMIT 1000; Your index follows_followinglist_followerid_createdat_idx looks good for the job. In Postgres 9.2+ it might get a bit faster if you append "followeeId" to the index - if ...


0

I think you've answered your own question. If there's no need for foreign key consistency and you are only ever reading back this data then the difference in search times makes a compelling argument for storing the history data as plain text rather than in a database.


1

This kind of queries do not need variables in Postgres. You can use window functions: WITH cte AS ( SELECT id, 1 + (ROW_NUMBER() OVER (ORDER BY id DESC) - 1) / $n AS new_x FROM t ) UPDATE t SET x = new_x FROM cte WHERE t.id = cte.id ; You 1 + (ROW_NUMBER() OVER (ORDER BY id DESC) - 1) / $n could also be written similarly to your expression, if ...


0

The trick is to uncheck the paginate results checkbox before executing the query. This creates that SELECT COUNT(*) ... wrapper. Without that checkbox selected, creating the plpythonu query is working as expected. I created the bug report #449 on sourceforge to see if this is really the desired behaviour.


3

The manual in Rules on INSERT, UPDATE, and DELETE) describes the INSTEAD mechanism for the context of your rule as: Qualification given and INSTEAD the query tree from the rule action with the rule qualification an the original query tree's qualification; and the original query tree with the negated rule qualification added Overlooking the ...


1

I took a look at triggers and came up with the following alternative to the rule: CREATE OR REPLACE FUNCTION fn_delete_cat() RETURNS trigger as $$ BEGIN IF old.deleted_at IS NULL THEN UPDATE cats SET deleted_at = NOW() WHERE cats.id = old.id; RETURN NULL; ELSE RETURN OLD; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER ...


0

It sounds like streaming replication with a hot standby is the solution you're looking for. Streaming Replication details the steps of how you go about setting it up. The hot standby option that they go over (they have it put to "on" which is what you will want) will allow you to run SELECTS against the 2nd database. Under this setup, however, your ...


2

Try an extra layer of quotes around your variables: psql -d mydb -U me -h localhost -f db_log.sql -v db_user_string="'Me'" -v version="'1.7.3'"


0

I think currently you are not able to log into the postgresql database? If this is the case you can get access of the database by changing the access authorization. Please open pg_hba.conf and change the following line: local all postgres md5 to: local all postgres ...


1

\c prints something like You are now connected to database "foobar" as user "squanderer". Use this if you don't mind creating a new connection, because this is what happens. The \connect (shortened as \c) without all parameters will create a new connection identical to your current one. The current connection is closed. See the \connect command spec on ...


2

As suggested by @Josh Kupershmidt and @JoeNahmias the solution is to use UNIQUE on md5 hash of the long value. However PostgreSQL 9.3 doesn't support expressions in UNIQUE constraints so an index, that supports expressions, has to be used: create unique index unique_data_url_index on mytable (md5(data_url));


1

PostgreSQL builds an index to support the UNIQUE constraint. You cannot index a field that large. As @josh-kupershmidt suggested, create the constraint on a hash of the field and you should be alright, barring hash collisions.


2

Well, your query here: return NOT Exists ( Select 1 FROM example_table AS a, example_table AS b WHERE a.id != b.id AND a.value = b.value); is not going to see the not-yet-inserted row, which is why it returns true on this second INSERT in your example: insert into example_table (id, value) values (1,0); Now, you could almost fix this by ...


0

Your function and the query in it check the existing values in the table. You could create a trigger function that can check the values to be inserted. I would recommend a simple unique constraint instead: create table example_table ( id integer not null, value integer not null unique, constraint pk primary key (id)); or constraint to existing ...


0

You can simply create a SEQUENCE and advance it randomly with function random() between the inserts, like this: CREATE SEQUENCE serial_id; INSERT INTO your_table (id, name) VALUES (nextval('serial_id'), 'textext'); SELECT nextval('serial_id'), generate_series(1,(random()*10)::integer); INSERT INTO your_table (id, name) VALUES (nextval('serial_id'), ...


0

Obviously, you are dealing with the same tables in each user schema. Have you considered inheritance for this? It can give you the best of both worlds for some use cases. There are also some limitations. You can have a separate schema for each user and still to search all user tables at once very conveniently. Related: Select rows from table where each ...


3

Not sure why you are not updating directly with additional predicates in the where clause, but if that is not possible you can select for update as in: SELECT * FROM tbl WHERE id = 1 FOR UPDATE This will lock the selected row and prevent updates of it while you do your validation.


0

You could put a lock on the table while you do the validation. BEGIN WORK; LOCK TABLE tbl IN exclusive mode; select * from tbl where id = 1; --Do your validation update tbl set .. where id = 1; COMMIT WORK;


0

I came up with the following query: with city_before as ( select person, city from ( select person, timestamp, first_value(timestamp) over (partition by person order by timestamp desc) as latest_timestamp, first_value(city) over (partition by person order by ...


0

I think it would have to be: if($type[$i]=='g') Etc. But you don't need any of this. Don't loop at all. Use a single query with a join and an aggregate function to generate the array: SELECT d.interested, d.hours, d.frequency, d.type , array_agg(CASE d.type WHEN 'g' THEN u.schedule_l WHEN 'h' THEN u.schedule_h ...


0

I actually had a similar question and found a simple solution that worked for me. I took Multicore and wrote a couple of lines of python in order to import a REST API: Given a REST API curl -H "authentication:ZQzBedExV8YGEztzUJdfqe0nsGNGdstZ" -H "Content-Type: ap/json;charset=UTF-8" -X POST http://127.0.0.1:3000/system_module/list.json -d '{}' I ...


1

Like @dezso commented, creating a new table and dropping the old used to be faster in old versions, but not any more with the new implementation in pg 9.1. The most common problem with CLUSTER is that it requires an exclusive lock on the table, which does not go well with concurrent access to it. The solution to this problem is pg_repack, which does not ...


2

I assumed you were using a pl/pgsql block (probably a function). For the generic case, you can use record, it can basically take a row of any structure, often used like DECLARE i record; BEGIN FOR i IN SELECT this, that, something, else FROM some_table LOOP ... END LOOP; There is also a possibility of defining a view: CREATE VIEW ...


1

You can get all open dblink connections using dblink_get_connections(). Those that are not used can only be found by examining your function code like Erwin suggests. I thought that with SQL functions one can query for the dblink functions it depends on, but it happens not to be the case (actually, you can even drop the extension while having functions ...


1

The post you found is from 2007. Rather start with the current manual: When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the ...


1

You have hugely overcomplicated the solution. SQL code After removing much of the cruft, it boils down to this: DO $do$ DECLARE customer_schema text; BEGIN FOR customer_schema IN SELECT cname FROM customer LOOP EXECUTE format('COPY ( SELECT p.*, t1.*, t2.* -- etc. Or just: * FROM %1$I.portal p ...


1

Adding a new value into a unique index locks out (until commit) any other transaction from inserting the same value. When doing that with multiple values in the same transaction, these locks accumulate. Then when several parallel transactions do that concurrently in no particular order, that creates the opportunity for transactions to inter-lock each other, ...


1

You can use pg_stat_replication along with pg_xlog_location_diff. Checking the differences can tell you how many bytes of xlog have flown from the master to the slave.


0

what you are doing is maybe not the right approach. what you want is ... COPY (SELECT ...) TO 'some_file_name' CSV; the crux might be your loop. you can get rid around that one easy: there is a function called unnest, which can transform an array into a table which can then be joined or whatever (via a LATERAL join maybe). you are on the wrong path with ...


0

After some processing this boiled down to: While your predicate d."SettlementPointName" = 'John' is filtering a single value for "SettlementPointName" anyway, simplify to: SELECT count( d."SettlementPointPrice" < 10.5 OR NULL) AS da_00_10 , count(d."SettlementPointPrice" >= 10.5 AND d."SettlementPointPrice" ...


0

I am using nethogs command to tracking WAL send/receive between master and slave. Download: nethogs for centos link Install: rpm -ivh nethogs-0.8.0-1.el6.x86_64.rpm Tracking: nethogs eth0 Ref: 18 commands to monitor network bandwidth on Linux server


0

First thing you have to separate here are the words query, transaction and connection. Clue: your query gets executed - it is in the active state. After that the query ends but the connection stays on - the idle state. There is no transaction (it has been committed) so the xact_start is null. So you have to close the connection after the query is ...


0

I'd imagine using JOINs rather than subqueries would be the first step to enhancing the speed of this thing. Very rough example: SELECT p.* FROM product p INNER JOIN products_categories pc ON p.product_id = pc.product_id INNER JOIN public.users_categories c ON pc.category_id = c.category_id WHERE p.disabled = False AND c.user_id = 'some-user-id' Not gone ...


0

For anyone looking for a workaround, limiting pg_restore to a specific schema has helped me get around this bug. See http://stackoverflow.com/a/11776053/11819


1

You are not doing anything wrong per se, and the documentation is not wrong. But the initscript from CentOS doesn't support you doing things this way, so you have to change the initscripts to not check for these files, since PostgreSQL will check for it itself and just try to start the database even if these files are not there. So to test it out, comment ...


1

In plpgsql code (not in plain SQL!): EXECUTE format($$COPY (select * from %I.portal) TO '/tmp/out1.csv' WITH CSV$$, cname); cname being the variable holding a (yet unescaped) schema name. You need to understand: dollar quoting the format() function SQL injection and how to defend against it: SQL injection in Postgres functions vs prepared queries ...


1

You could use inheritance for this. You would have a "master" schema with template tables and only the basic set of columns that all inheriting tables share: CREATE TABLE master.portal(portal_name text); All other tables in various schemata inherit from it, while possibly adding more local columns: CREATE TABLE a.portal(portal_id serial PRIMARY KEY, col2 ...


1

Three misunderstandings: You cannot return data from a DO command. You cannot SELECT without target in plpgsql code. That's what the error message tells you. You don't need either for a simple SELECT statement. Just run the statement itself: abc() { $PG_CMD 'select * from customer' }


1

Yes, as long as you install version 9.1 (minor version doesn't matter). And normally you don't need to copy/move them anywhere, the installation will notice there is already a database system set up and will use it. Of course taking a backup is never a bad idea. Then again, the reason for it being offline should be clear from PostgreSQL logs. Memory ...


0

@Akash already gave an accurate explanation. As for your second case: SELECT * FROM kodiall WHERE numfield=NULL This is a gross misunderstanding of the NULL value. Per definition, nothing equals NULL, not even NULL. numfield=NULL always returns NULL, and only TRUE qualifies in a WHERE condition. So you get 0 rows for this, always. Postgres doesn't need ...


4

While you EXPLAIN your query, in the first instance, you can see that the planner estimates the total row count to 1 Index Scan using mytable_numfield_idx on mytable (cost=0.00..8.27 **rows=1** width=193) Using a index in this case is beneficial as there are hardly any rows matching your WHERE condition. In the second case, there are around 1080 rows ...


1

I would draw a text backup and search it with vim or any tool of your choice. As far as plpgsql functions are concerned (there is one behind every trigger), you can query the system catalog pg_proc: SELECT * FROM pg_catalog.pg_proc WHERE prosrc ILIKE '%dblink%';


0

And what about: SELECT * FROM ts_stat('SELECT thing FROM test') ORDER BY nentry DESC, ndoc DESC, word LIMIT 100; Works at a glance for me (pg9.1) an shows a Hitlist of words used in the documents.


0

It started working again. One of the following may have un-clogged it: I ran a backup on the master removed ssl_renegotiation_limit config Waited about 5 minutes added ssl_renegotiation_limit to slave config



Top 50 recent answers are included