[GENERAL] Trying to understand page structures in PG

Hello I understand that when an update of say 100,000 rows are made, PG writes the updated rows as a new row. These new rows are not visible to any sessions except the one creating it. At commit time PG flips something internally to make these rows visible to all. My Q: what happens to those rows

Re: [GENERAL] Exclude constraint using custom operator

Stated differently, is there a simple way to extend or "subclass" the builtin range_ops operator family, adding my own operator to it, so that I can use it in an exclude index? I couldn't find a SQL definition for the range_ops family, probably because it's defined and implemented in C and head

Re: [GENERAL] Trying to understand page structures in PG

Rakesh Kumar wrote: > I understand that when an update of say 100,000 rows are made, PG > writes the updated rows as a new row. These new rows are not visible > to any sessions except the one creating it. At commit time PG flips > something internally to make these rows visible to all. > > My Q: w

[GENERAL] what database schema version management system to use?

Hi all, I am searching for a proper database schema version management system. My criteria are the following: 0) Open-source, supports postgresql 1) Uses psql to execute changesets (to have no problems with COPY, transaction management or sophisticated DDL commands, and to benefit from scripti

Re: [GENERAL] what database schema version management system to use?

On 06/04/2016 13:55, Alexey Bashtanov wrote: Hi all, I am searching for a proper database schema version management system. My criteria are the following: 0) Open-source, supports postgresql 1) Uses psql to execute changesets (to have no problems with COPY, transaction management or sophistica

Re: [GENERAL] Exclude constraint using custom operator

Tobia Conforto writes: > Stated differently, is there a simple way to extend or "subclass" the builtin > range_ops operator family, adding my own operator to it, so that I can use it > in an exclude index? No. In a GIST opclass, the set of supported operator strategies is determined by the cod

Re: [GENERAL] what database schema version management system to use?

On 04/06/2016 03:55 AM, Alexey Bashtanov wrote: Hi all, I am searching for a proper database schema version management system. My criteria are the following: 0) Open-source, supports postgresql 1) Uses psql to execute changesets (to have no problems with COPY, transaction management or sophisti

Re: [GENERAL] Trying to understand page structures in PG

On 04/06/2016 02:39 AM, Rakesh Kumar wrote: Hello I understand that when an update of say 100,000 rows are made, PG writes the updated rows as a new row. These new rows are not visible to any sessions except the one creating it. At commit time PG flips something internally to make these rows vis

Re: [GENERAL] what database schema version management system to use?

Alexey Bashtanov schrieb am 06.04.2016 um 12:55: > I am searching for a proper database schema version management system. > > My criteria are the following: > 0) Open-source, supports postgresql > 1) Uses psql to execute changesets (to have no problems with COPY, > transaction management or sophi

Re: [GENERAL] Trying to understand page structures in PG

On Wed, 2016-04-06 at 10:33 +, Albe Laurenz wrote: > Every row has two system columns associated with it: xmin and xmax > > xmin is the transaction ID that created the row, while xmax is > the transaction ID that removed the row. > > So when an update takes place, xmax of the original row a

Re: [GENERAL] what database schema version management system to use?

On Wed, Apr 6, 2016 at 3:55 AM, Alexey Bashtanov wrote: > Hi all, > > I am searching for a proper database schema version management system. > > My criteria are the following: > 0) Open-source, supports postgresql > 1) Uses psql to execute changesets (to have no problems with COPY, > transaction m

[GENERAL] Multi-row constraints, how to avoid unnecessary trigger execution?

I have a complex data validation requirement that spans many rows and possibly more than one table. The application must be able to perform several data manipulation statements that could invalidate the requirement between one another, and only have the database check this requirement at transa

Re: [GENERAL] Multi-row constraints, how to avoid unnecessary trigger execution?

On 6 April 2016 at 18:32, Tobia Conforto wrote: > I have a complex data validation requirement that spans many rows and > possibly more than one table. > > The application must be able to perform several data manipulation > statements that could invalidate the requirement between one another, and

Re: [GENERAL] Multi-row constraints, how to avoid unnecessary trigger execution?

På onsdag 06. april 2016 kl. 18:32:50, skrev Tobia Conforto < tobia.confo...@gruppo4.eu >: I have a complex data validation requirement that spans many rows and possibly more than one table. The application must be able to perform several data manipulation state

Re: [GENERAL] Multi-row constraints, how to avoid unnecessary trigger execution?

Sándor, I'd rather have the application developers use regular DML, which could become quite complex, and just perform my check on the database side, at transaction commit time. Andreas, thanks, but I need to avoid duplicate executions on different rows too. I just came up with this "hack" whic

Re: [GENERAL] Multi-row constraints, how to avoid unnecessary trigger execution?

På onsdag 06. april 2016 kl. 19:00:05, skrev Tobia Conforto < tobia.confo...@gruppo4.eu >: Sándor, I'd rather have the application developers use regular DML, which could become quite complex, and just perform my check on the database side, at transaction commit

Re: [GENERAL] comparing two JSON objects in 9.3

There's also another way using hstore instead of json: https://wiki.postgresql.org/wiki/Audit_trigger_91plus -- View this message in context: http://postgresql.nabble.com/comparing-two-JSON-objects-in-9-3-tp5803617p5897128.html Sent from the PostgreSQL - general mailing list archive at Nabble.c

[GENERAL] PostgreSQL 9.5.1 -> PG_REWIND NOT FOUND

Hello, I have installed PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit on Ubuntu 14.04.04 from http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main 9.5 Now after replication failure I want use new pg_rewind but it seems that pg_rewind is not

[GENERAL] database is not accepting commands to avoid wraparound data loss in database

Hi, I'm following error in my DBMS, < 2016-04-05 17:02:42.053 BRT >ERROR: database is not accepting commands to avoid wraparound data loss in database "zabbix" < 2016-04-05 17:02:42.053 BRT >HINT: Stop the postmaster and vacuum that database in single-user mode. I'm running the vacuum in sing

Re: [GENERAL] I can't see wal receiver process in one node

Hi Sameer Yesterday I reviewed the configuration in both servers. Configured each with the restore_command and archive_cleanup_command (to clean wal archives). But I saw in the Node B (only this node) have active the parameter named recovery_target_timeline = 'latest', this was because the one

Re: [GENERAL] Problem after replication switchover

> I'd guess it's probably more like option 3 - Glusterfs ate my database. Hi, thanks for your reply! We do archive logs on a distributed Glusterfs volume in case the streaming replication gets too far behind and the transaction logs have been removed. Would a restore of a corrupt archived log fil

Re: [GENERAL] Problem after replication switchover

On Wednesday, April 06, 2016 10:33:16 AM Lars Arvidson wrote: > > I'd guess it's probably more like option 3 - Glusterfs ate my database. > > Hi, thanks for your reply! > We do archive logs on a distributed Glusterfs volume in case the streaming > replication gets too far behind and the transactio

Re: [GENERAL] PostgreSQL 9.5.1 -> PG_REWIND NOT FOUND

On 04/06/2016 04:02 AM, sgringer wrote: Hello, I have installed PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit on Ubuntu 14.04.04 from http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main 9.5 Now after replication failure I want use new pg_r

[GENERAL] Deadlock between VACUUM and ALTER TABLE commands

Hello, Occasionally our test suite gets into a deadlocked state. I was curious why this happens, and what our options are for dealing with it. We run ~2900 tests on an Ubuntu machine in sequence against a Postgres 9.4.6 database. There are about 60 tables; each test runs ~3 queries, and the larges

Re: [GENERAL] pg_upgrade error regarding hstore operator

Thanks for the assist Tom. That worked for us. Noticing a different issue following the pg_upgrade. If we take a pg_dump of a database on this upgraded instance with the hstore extension and try to pg_restore it back up to the same instance we get the following errors (ignore the likeness to you

Re: [GENERAL] pg_upgrade error regarding hstore operator

"Feld, Michael (IMS)" writes: > Thanks for the assist Tom. That worked for us. Noticing a different > issue following the pg_upgrade. If we take a pg_dump of a database on > this upgraded instance with the hstore extension and try to pg_restore > it back up to the same instance we get the followin

Re: [GENERAL] what database schema version management system to use?

On Wed, 6 Apr 2016 11:55:40 +0100 Alexey Bashtanov wrote: > Hi all, > > I am searching for a proper database schema version management system. > > My criteria are the following: > 0) Open-source, supports postgresql > 1) Uses psql to execute changesets (to have no problems with COPY, > transac

Re: [GENERAL] database is not accepting commands to avoid wraparound data loss in database

On Wed, Apr 6, 2016 at 8:13 AM, pedrovlf wrote: > Hi, > > I'm following error in my DBMS, > > < 2016-04-05 17:02:42.053 BRT >ERROR: database is not accepting commands to > avoid wraparound data loss in database "zabbix" > < 2016-04-05 17:02:42.053 BRT >HINT: Stop the postmaster and vacuum that >

Re: [GENERAL] Trying to understand page structures in PG

On Wed, Apr 6, 2016 at 8:35 AM, rob stone wrote: > On Wed, 2016-04-06 at 10:33 +, Albe Laurenz wrote: > > >> Every row has two system columns associated with it: xmin and xmax >> >> xmin is the transaction ID that created the row, while xmax is >> the transaction ID that removed the row. >> >

Re: [GENERAL] what database schema version management system to use?

On 4/6/2016 3:55 AM, Alexey Bashtanov wrote: I am searching for a proper database schema version management system. At my $job we're old school. our schemas are versioned. there's a settings table with (setting TEXT, value TEXT) fields, a row in that is ('version', '1.0') or whatever.

Re: [GENERAL] PostgreSQL 9.5.1 -> PG_REWIND NOT FOUND

I have found it in this folder /usr/lib/postgresql/9.5/bin the command "pg_rewind" don't work globally like othe pg -- View this message in context: http://postgresql.nabble.com/PostgreSQL-9-5-1-PG-REWIND-NOT-FOUND-tp5897145p5897332.html Sent from the PostgreSQL - general mailing list archive

Re: [GENERAL] PostgreSQL 9.5.1 -> PG_REWIND NOT FOUND

On 4/6/2016 11:31 PM, sgringer wrote: I have found it in this folder /usr/lib/postgresql/9.5/bin the command "pg_rewind" don't work globally like othe pg $ export PATH=/usr/lib/postgresql/9.5/bin:$PATH $ pg_rewind --help -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-