Tag Info

New answers tagged

0

It'd help more if you could be more specific about what you're modeling, as it's hard to picture at the moment, for me at least. For tree structures, in SQL, any database that supports Recursive Common Table Expressions is a good choice (any major one other than MySQL). For historical data, the History Table pattern has proven to be effective. For table ...


3

It looks like you've run into the issue that jsonb columns have a flat 1% statistics rate, as reported here Working around jsonb's lack of stats?. Looking at your query plans, the differences between the estimates and the actual executions are huge. The estimates say there are probably 200 rows, and the actual return 100158 rows, which causes the planner to ...


1

You can run that, no problem: VACUUM FULL ANALYZE pg_largeobject; Might even remove some dead rows. Details: VACUUM returning disk space to operating system But it's probably not going to solve your actual problem. When using the large object facility of Postgres, large objects ("blob": binary large object) themselves are broken up in chuncks of ...


0

The source of the issue was the name of one colunm of the table: PCF_index psql generated "PCF_index" when exporting the schema. This caused trouble to Squirrel when trying to read it. After the column is renamed pcf_index, the issue was solved.


-1

I have good experience with Full Convert.


0

I think it should be; DECLARE _port int; BEGIN select alter_port into _port from a_table where ....; select data into data_list from a_table where PortNumber = _port;


0

To answer your questions: Set the checkpoint_timeout lower, as you did, to 30 seconds or so. I made sure to enable log_checkpoints in my postgresql.conf and used check_postgres.pl to monitor the number of segments and replication lag. You can also set checkpoint_completion_target=0 to adjust the checkpointing behavior as well. But really, on a replica, ...


0

There are several places you'd want to change the IP address for a PostgreSQL primary server. Most of them are actually outside of PostgreSQL itself or its configuration, though. You already mentioned the pg_hba.conf so that doesn't need to be mentioned. Tables that you have inet types for, or other columns you store IP addresses in. If you found IP ...


0

The simple way to do that would be to call your bar() function like this: postgres@[local]:5432:postgres:=# SELECT * FROM bar(); a | b ---+--- 1 | 2 3 | 4 (2 rows) Time: 0.622 ms postgres@[local]:5432:postgres:=# Which returns as a record type in this context, with two rows and two columns. Hope that helps. =)


3

Problem This is a more complex problem than is obvious on a quick glance. You are sorting by two columns, each from a different table, while you join on two other columns. This makes it impossible for Postgres to use the provided indexes and it has to default to (very) expensive sequential scans. Here is a related case on dba.SE: Can spatial index help a ...


0

As simple answer to the question, yes, it is possible to do this with a function. PostgreSQL has several very powerful function languages that you can use for this purpose. Being powerful, you can do some pretty neat things with them. You can also shoot yourself in the foot very easily. The solution is very brute-force and inelegant, and I wouldn't ...


2

You can use a CTE to supply the values: with data (street, city, user_id) as ( values ('street1','LA', 2) ) insert into addresses(street, city, user_id) select * from data where exists (select * from users where users.id = data.user_id and users.storeaddress = true); Or alternatively a derived table: insert ...


1

The best way I think would be to use the LAG() or LEAD() functions: SELECT *, - 100.0 * (1 - LEAD(Price) OVER (ORDER BY t.Id) / Price) AS Grown FROM table_name AS t ORDER BY t.Id ; With LEAD(Price) OVER (ORDER BY t.Id) you have access to the next Price when the rows are ordered by Id. It's not clear what the order should be. Based on the data in ...


-1

You can use Common Table expression, WITH Data as (select ROW_NUMBER() OVER (ORDER BY applicable_date) as RowId, date, qty, price, [value] from tbl ) SELECT CurrentRow.date, CurrentRow.qty, CurrentRow.price, CurrentRow.[value], 'Your calculation here for Growth' FROM Data as CurrentRow LEFT OUTER JOIN Data as NextRow ON NextRow.RowId = CurrentRow.RowId + ...


0

Yes, this should work (untested): CREATE OR REPLACE FUNCTION trfn_tbl_log_timetypespan() -- generic name RETURNS trigger AS $func$ DECLARE _timetype varchar; _timetypespan_resume interval; _ct int; BEGIN CASE NEW.timetype WHEN 'lap' THEN EXECUTE format($$ SELECT timetype, timetypespan, age($1, timestmp) FROM %s WHERE ...


4

To give you a flavour of JOINs and SQL, I created two tables - Customer and Cust_Order as shown. I then loaded these tables with data (see end of post for DML). These examples use both PostgreSQL and MySQL. A note on table names. I use singular names - you can, of course, use plural (as many do) - but decide and stick to one! A word of advice (and see ...


1

Use pg_restore to list its contents, dump it to SQL, or restore it to a database.


1

Assuming that, for the same trigger invocation, you take all the values from the same row in the table firing your trigger, your trigger function could look like this: CREATE OR REPLACE FUNCTION trfn_tbl_log_any() RETURNS trigger AS $func$ DECLARE _ct int; BEGIN IF NEW.timetype = 'start' THEN EXECUTE format($$ SELECT floor(t.timeidx) + 1 ...


1

Or you can use TG_RELID, but since its data type is plain oid, not regclass, one must cast it to regclass explicitly to get the auto-conversion to a schema-qualified (only if the current search_path requires it), cleanly escaped table name. The documentation: TG_RELID Data type oid; the object ID of the table that caused the trigger invocation. ...


2

The actual syntax corresponding to the imaginary SELECT columnname FROM %currenttable% would be, in plpgsql: execute format('SELECT columnname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME); The TG_* built-in variables are documented in Trigger Procedures and the execute and format plpgsql constructs in Basic Statements. The query above is ...


0

Use pg_dump -Fc. That'll create a pre-compressed dump suitable for restoration with pg_restore. Remember to check the exit code of pg_dump. If you want to encrypt it you'll need to that as a second pass afterwards using the encryption tool of your choice.


2

I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables TG_TABLE_SCHEMA and TG_TABLE_NAME, or use TG_RELID. These, alongside EXECUTE for dynamic SQL, let you do what you want: BEGIN EXECUTE format('SELECT colname FROM %I', TG_RELID) END; or BEGIN EXECUTE format('SELECT colname FROM %I.%I', ...


1

work_mem Obviously, the sort operation spills to disk: Sort Method: external merge Disk: 36224kB More work_mem can help the query, like @Kassandry already suggested. Increase the setting until you see Memory instead of Disk in the EXPLAIN output. But it's probably a bad idea to increase the general setting based on one query. Proper setting depends on ...


1

One of the easiest and most effective ways to increase performance on this sort of query is to execute SET work_mem=40MB (because you have ~32MB of temp file for sorting, and a little extra often helps) then run your query, and see if the EXPLAIN ANALYZE plan changes from disk to an in-memory sort. Afterwards, run RESET work_mem to put the value back to the ...


0

An important distinction to make between the two claims compared in the question is that the first is a rough formulation for the number of active connections at any one time. The second claim is for the setting that you put in place for the maximum allowed that Postgres will accept. These are two separate things. When you go back and read the Optimal ...


6

You don't need triggers or PL/pgSQL at all. You don't even need DEFERRABLE constraints. And you don't need to store any information redundantly. Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the "chicken/egg" problem of inserting a user and his active email, ...


0

But I think that something is wrong in the joins. Make a query and get the id took 5 seconds, and I think that this is not normal. See for example this query: explain (analyze, buffers) select p.account, p.direction, r.cost, r.usage, p.answer_time, c.timespans, e.extra_fields from cdrs_primary p join rated_cdrs r on p.cgrid = ...


1

The first, most important, thing you can do is get rid of that use of json fields. If you know the field you'll be querying in advance, preferably make it a real column. At the cost of inefficiently widening the table you can do that with a generated column using a trigger, then index the generated column. It'll hurt your insert performance a bit, but you ...


1

Depending on how selective the combined predicates are, I would imagine a good index for this particular query would be: CREATE INDEX index_name ON products (above_revenue_average ASC, start_date DESC) WHERE status > 100 AND category_id <> 5; The SELECT * is potentially problematic because the index above does not contain all columns. ...


5

If you can add a column to the table, the following scheme would almost1 work: CREATE TABLE emails ( UserID integer NOT NULL, EmailAddress varchar(254) NOT NULL, IsActive boolean NOT NULL, -- New column ActiveAddress varchar(254) NOT NULL, -- Obvious PK CONSTRAINT PK_emails_UserID_EmailAddress PRIMARY KEY (UserID, ...


0

If you have errors in your log along the lines of SSL Renegotiation Failure or SSL Error, then you likely ran into an issue with a version of OpenSSL and SSL renegotiation during the streaming basebackup. Setting ssl_renegotiation_limit = 0 should help address your problem. There are and have been many discussions now on disabling ssl renegotiation by ...


4

The only way to do either of these without schema changes is with a PL/PgSQL trigger. For the "exactly one" case, you can make the references mutual, with one being DEFERRABLE INITIALLY DEFERRED. So A.b_id (FK) references B.b_id (PK) and B.a_id (FK) references A.a_id (PK). Many ORMs etc cannot cope with deferrable constraints though. So in this case you'd ...


0

You might want to try to set up max_wal_senders on your replica, and use --xlog-method=stream instead of --xlog-method=fetch, which would let the basebackup grab the individual WAL records as they are shipped, rather than trying to grab the WAL segments at the end of the basebackup. If you have to use --xlog-method=fetch, then you should set ...


1

You can't download a snapshot from RDS you have to use a tool like pg_dump This has already been answered multiple times on regular Stack Overflow: http://stackoverflow.com/questions/14916899/download-rds-snapshot


2

Maybe this is just over-engineered. Have you actually tried using a single full index? Partial indices covering the whole table together do not provide much gain, if any, for index lookups, and from your text I infer that you have indices for all run_ids? There may be some advantages to index scans with partial indices, still I would benchmark the simple ...


2

You can avoid various complications by passing values as values with the USING clause: CREATE OR REPLACE FUNCTION foo(linktable regclass, inttable regclass, verttable regclass) RETURNS void AS $func$ BEGIN EXECUTE format( 'SELECT tdgSetTurnInfo($1, $2, $3, array_agg(t.id)) FROM %s t' ...


0

Well, it would have been a lot faster and easier with 9.4, but it's doable with 9.3 in simple situations. SELECT ('{' || -- Opening brace for the JSON object string_agg( -- Get all the key/value pairs together in the same string concat_ws(':', '"' || a || '"', b || ''), ',') || '}')::json -- Concatenate values to "key" : "value" format FROM ( -- ...


0

Make sure PostgreSQL Server is allowed through the firewall: On the server machine, go to Control Panel > System and Security > Windows Firewall > Allow an app through Windows Firewall Scroll through Allowed apps and features, make sure 'PostgreSQL Server' is checked.


4

psycopg2 is a wrapper around libpq, written in C, to expose a Python DB-API compatible API to Python programs. It implements Python objects in C that call libpq functions. It has a thin Python module wrapper around it to load it and provide some of the interface functionality that's easier to write in pure Python. Anything that implements the DB-API to ...


1

You could always double check your problem by issuing a command such as /usr/bin/pgsql/bin/createdb test You can frequently get better/clearer error messages and/or other information from the command line than from GUI tools such as pgAdmin III. But, in order to change a system parameter, you have to reload the postgresql.conf file after making changes. ...


1

Working with this setup: CREATE TABLE tbl ( tbl_id serial PRIMARY KEY , user_id text NOT NULL , start_time timestamp NOT NULL -- if values could be NULL ... , end_time timestamp NOT NULL -- ... it would be more complicated ); Total duration without redundancy Your numbers indicate you just want the total duration without counting ...


2

I don't think there's a fundamental limitation there, and it's something I'd like to see addressed. I agree with you that it's a bit ugly. That said, the convention to just use the postgres database as a maintenance database is so strong that the real world impact is negligible. PostgreSQL has shared catalogs, things like pg_authid and pg_database. It's ...


1

While FOREACH is very convenient to loop through a single array, it's not particularly useful to step through multiple arrays in parallel. Use a plain FOR loop with array_lower() / array_upper() instead: FOR i IN 1 .. array_upper(UserResponseList, 1) LOOP RAISE NOTICE '%, %, %', QuestionList[i], UserResponseID_List[i], UserResponseList[i]; END LOOP; ...


2

Well, apparently this is it: SELECT *, SUM(amount) OVER ( ORDER BY tx_date DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS running_balance FROM transactions ORDER BY tx_date DESC; Output: id | memo | amount | tx_date | running_balance ----+-----------------+--------+------------+----------------- ...


2

You're restoring with pg_restore --format=c ... but the pg_dump was not done with --format=c, it was done with the default, plain format. From pg_dump manpage: -F format, --format=format Selects the format of the output. format can be one of the following: p, plain Output a plain-text SQL script file (the default). ...


7

First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. Your life gets simpler if you just live with it. To get gap-less numbers, the (often better) alternative is to use a VIEW with row_number(). Example in this related answer: Gap-less sequence where multiple transactions with multiple tables are involved Here ...


3

This would seem rather simple: INSERT INTO user_flags (user_id, flag) SELECT u.id, 'myflag' -- or whatever to write to this field FROM users u LEFT JOIN user_flags uf ON uf.user_id = u.id WHERE uf.user_id IS NULL; -- to exclude already existing How to .. Select rows which are not present in other table While this is susceptible to race ...


1

Given this table: CREATE TABLE usr ( usr_id serial PRIMARY KEY , usr text NOT NULL , tz text -- time zone names ); Use the AT TIME ZONE construct: SELECT *, (now() AT TIME ZONE tz)::time AS local_time FROM usr WHERE (now() AT TIME ZONE tz)::time BETWEEN '13:00'::time AND '23:00'::time; Including upper and lower bounds 1 PM and 11 PM. ...


2

Yes, they are. shared_buffers is block-oriented, and stored in the same format as on-disk. This is generally good for performance, since the decompression is very fast, and the compression means that more fits in shared_buffers. The only case it might hurt is if you have enough storage to fit the whole dataset in shared_buffers uncompressed too.



Top 50 recent answers are included