[GENERAL] update error with serializable
Hi, I've two threads countinuously updataing rows in the same table. Each one does: BEGIN, UPDATE,UPDATECOMMIT There can't be two active transactions updating the same row (my bug apart but I don't think so). I'm using default_transaction_isolation = 'serializable' I get "could not serialize access due to read/write dependencies among transactions" I din't expect to see it, hence there must be something in postgresql theory that I haven't understood well and I'd like a clarification. Below here a log of commands issued by my threads followed by the error msg from PG server. Thanks Pupillo Log: thread 0: BEGIN thread 1: BEGIN thread 0: UPDATE stato SET dati=$1 WHERE id=0; thread 0: UPDATE stato SET dati=$1 WHERE id=1; thread 1: UPDATE stato SET dati=$1 WHERE id=10; thread 0: UPDATE stato SET dati=$1 WHERE id=2; thread 1: UPDATE stato SET dati=$1 WHERE id=11; thread 1: UPDATE stato SET dati=$1 WHERE id=12; thread 0: UPDATE stato SET dati=$1 WHERE id=3; thread 1: UPDATE stato SET dati=$1 WHERE id=13; thread 0: UPDATE stato SET dati=$1 WHERE id=4; thread 1: UPDATE stato SET dati=$1 WHERE id=14; thread 0: UPDATE stato SET dati=$1 WHERE id=5; thread 1: UPDATE stato SET dati=$1 WHERE id=15; thread 1: UPDATE stato SET dati=$1 WHERE id=16; thread 0: UPDATE stato SET dati=$1 WHERE id=6; thread 1: UPDATE stato SET dati=$1 WHERE id=17; thread 0: UPDATE stato SET dati=$1 WHERE id=7; thread 1: UPDATE stato SET dati=$1 WHERE id=18; thread 1: UPDATE stato SET dati=$1 WHERE id=19; thread 0: UPDATE stato SET dati=$1 WHERE id=8; thread 0: UPDATE stato SET dati=$1 WHERE id=9; thread 1: COMMIT thread 0:UPDATE ERROR ERROR: could not serialize access due to read/write dependencies among transactions DETAIL: Reason code: Canceled on identification as a pivot, during conflict out checking. HINT: The transaction might succeed if retried. STATEMENT: UPDATE stato SET dati=$1 WHERE id=9;
[GENERAL] Start/stop postgresql with pg_ctl or service without root access on RHEL
Hello We plan to use postgresql on RHEL 6. DBAs won't have root access but they will need to start/stop the databases. They'll be able to do so with pg_ctl as postgres. However databases should also start automatically when the server reboots and so we should use services (/etc/init.d/postgresql-9.x). When postgres is started with service (as root) and stopped with pg_ctl as postgres, the pid file in $PGDATA is deleted but not the specific pid and lock files (respectively in /var/run and /var/lock/subsys) created by the init.d script. It leads to an inconsistent state where service postgresql-9.x status throws"postgresql-9.x dead but pid file exists". So what is the best practice and solution for this situation? - ask sysadmin to give sudo /etc/init.d/postgresql-9.x to the DBAs and never use pg_ctl again? - remove the specific pid and lock files from the postgresql-9.x service script? - any other idea? Thank you in advance JM Scheiwiler
Re: [GENERAL] Start/stop postgresql with pg_ctl or service without root access on RHEL
Jean-Michel Scheiwiler writes: > We plan to use postgresql on RHEL 6. > DBAs won't have root access but they will need to start/stop the databases. > They'll be able to do so with pg_ctl as postgres. > However databases should also start automatically when the server reboots > and so we should use services (/etc/init.d/postgresql-9.x). Don't do that. > When postgres is started with service (as root) and stopped with pg_ctl as > postgres, the pid file in $PGDATA is deleted but not the specific pid and > lock files (respectively in /var/run and /var/lock/subsys) created by the > init.d script. > It leads to an inconsistent state where service postgresql-9.x status > throws"postgresql-9.x > dead but pid file exists". That's one reason why you shouldn't do that. There are others; for instance I don't think you end up with the same selinux context for the daemon if you don't go through "service start". > So what is the best practice and solution for this situation? I'd go with the restricted sudo approach. Customized init scripts are a pain --- either they get overwritten by package upgrades, or they fail to track changes in the script, and either result is bad. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] view dependent on system view caused an upgrade to fail
I have the following function and view in my db:
create or replace function ${catalogSchema}.fn_show_pg_stat_activity() returns
setof pg_catalog.pg_stat_activity as $$ select * from
pg_catalog.pg_stat_activity; $$ language sql volatile security definer;
create or replace view ${catalogSchema}.pg_stat_activity as select * from
${catalogSchema}.fn_show_pg_stat_activity();
During an upgrade from 9.5.4 to 9.6.1, the upgrade failed due to the error msg
below: obviously the structure of pg_catalog.pg_stat_activity has changed
between these 2 version.
pg_restore: creating VIEW "rcmmaster.pg_stat_activity" pg_restore: [archiver
(db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC
entry 205; 1259 1995821 VIEW pg_stat_activity rcm_master_user pg_restore:
[archiver (db)] could not execute query: ERROR: column reference "query" is
ambiguous
My question: seems like pg_dump and pg_restore duirng the pg_upgrade actually
used a hard-coded record/column set of the old verison of pg_stat_activity,
instead of just recompiling the function then the view after the system catalog
upgrade, so it would dynamically just retrieve the new column lists. Could this
be considered a bug/defect? Is there any way to work around it w/o a postgresql
fix?
Thanks,
Patricia
Confidentiality Notice:: This email, including attachments, may include
non-public, proprietary, confidential or legally privileged information. If
you are not an intended recipient or an authorized agent of an intended
recipient, you are hereby notified that any dissemination, distribution or
copying of the information contained in or transmitted with this e-mail is
unauthorized and strictly prohibited. If you have received this email in
error, please notify the sender by replying to this message and permanently
delete this e-mail, its attachments, and any copies of it immediately. You
should not retain, copy or use this e-mail or any attachment for any purpose,
nor disclose all or any part of the contents to any other person. Thank you.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] view dependent on system view caused an upgrade to fail
"Hu, Patricia" writes:
> I have the following function and view in my db:
> create or replace function ${catalogSchema}.fn_show_pg_stat_activity()
> returns setof pg_catalog.pg_stat_activity as $$ select * from
> pg_catalog.pg_stat_activity; $$ language sql volatile security definer;
> create or replace view ${catalogSchema}.pg_stat_activity as select * from
> ${catalogSchema}.fn_show_pg_stat_activity();
> During an upgrade from 9.5.4 to 9.6.1, the upgrade failed due to the error
> msg below: obviously the structure of pg_catalog.pg_stat_activity has changed
> between these 2 version.
Yeah.
> My question: seems like pg_dump and pg_restore duirng the pg_upgrade
> actually used a hard-coded record/column set of the old verison of
> pg_stat_activity, instead of just recompiling the function then the view
> after the system catalog upgrade, so it would dynamically just retrieve
> the new column lists. Could this be considered a bug/defect?
By our reading, this behavior is required by the SQL standard.
Your view wasn't saved as "SELECT * FROM ...", it was saved as
"SELECT datid, datname, ... FROM ..." because the standard says to
do it that way. On the other hand, the rowtype of the function
result *did* change in the upgrade. So then you had a problem
with the view definition referring to columns that don't exist
in the function output.
Even if the view had, contrary to spec, acted like "SELECT *",
that would just have moved the problem up one level to whatever
depends on the view. No doubt this is why the SQL committee
said to make it work like that --- at least it confines the
problem to the particular view.
> Is there any way to work around it w/o a postgresql fix?
You can't really get around the fact that if your application
looks at system catalog or view columns that change, your
application is going to need changes too.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] migrate Sql Server database to PostgreSql
-- David Rader dav...@openscg.com On Thu, Jan 19, 2017 at 8:56 AM, Kenneth Marshall wrote: > On Thu, Jan 19, 2017 at 03:29:34PM +1100, Venkata B Nagothi wrote: > > On Thu, Jan 19, 2017 at 6:17 AM, PAWAN SHARMA > > > wrote: > > > > > Hello All, > > > > > > I am using postgres 9.5 enterprise edition. > > > > > > i want to to how to migrate Sql Server database to PostgreSql. what are > > > the things required for migration and what are the cron and prons of > > > migration. > > > > > > > Well, how challenging the migration is going to be purely depends on > > complexity of the the database and the how the Applications. As far as i > > know, there is no open-source tool available to migrate SQL Server > database > > to PostgreSQL. You can try "sqlserver2pgsql" which migrates does not > > migrate procedures. > > > > Overall with my experience, i can say, migrating application would be > most > > challenging part. > > > > - Preliminary analysis on the database, its object types, data types and > > code complexity of the procedures. T-SQL procedures will need to > re-written > > and they can in-turn trigger application code changes which can be > > challenging. > > Data types and procedures can pose real challenges while migrating > > applications. > > - If applications being used against SQL Server are proprietary, then, > you > > need to ensure they work with PostgreSQL. > > - Evaluate the amount of Application code changes to be done, that will > > give you an idea on how difficult migration will be. > > > > Hope that helps ! > > > > > > Regards, > > > > Venkata B N > > Database Consultant > > Hi, > > For the procedure part, you might want to take a look at pgTSQL: > > https://bitbucket.org/openscg/pgtsql > > Here is another link with some examples. I have not tried them myself. > > https://www.bigsql.org/docs/pgtsql/ > > Regards, > Ken > > Hello - pgTSQL enables you to run some parts of T-SQL code in PostgreSQL. Coverage is not complete by any means. As others have written, it all depends on how much T-SQL code you have in your database and application. Migrating the schema and data is fairly painless. But the amount of code you have will determine whether it's a 1-day or 1-month exercise. -Dave
Re: [GENERAL] update error with serializable
On Fri, Jan 20, 2017 at 4:44 AM, Tom DalPozzo wrote: > I've two threads countinuously updataing rows in the same table. > Each one does: BEGIN, UPDATE,UPDATECOMMIT > There can't be two active transactions updating the same row (my > bug apart but I don't think so). > I'm using default_transaction_isolation = 'serializable' > I get "could not serialize access due to read/write dependencies > among transactions" > I din't expect to see it, hence there must be something in > postgresql theory that I haven't understood well and I'd like a > clarification. Most likely one or both transactions have have updated 3 or more tuples on a single page, causing the tuple locks for the transaction on that page to be combined into a single page lock for that transaction. This is intended to prevent the memory required for tracking predicate locks from growing too large. This threshold of 3 per page was entirely arbitrary and always seen as something which could and should be improved someday. That might happen for version 10 (expected to be released next year), since a patch has been submitted to make that configurable. https://www.postgresql.org/message-id/flat/d8joa0eh9yw@dalvik.ping.uio.no#d8joa0eh9yw@dalvik.ping.uio.no If you are able to build from source, you might want to test the efficacy of the patch for your situation. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] update error with serializable
On 01/20/2017 10:05 AM, Kevin Grittner wrote: On Fri, Jan 20, 2017 at 4:44 AM, Tom DalPozzo wrote: I've two threads countinuously updataing rows in the same table. Each one does: BEGIN, UPDATE,UPDATECOMMIT There can't be two active transactions updating the same row (my bug apart but I don't think so). I'm using default_transaction_isolation = 'serializable' I get "could not serialize access due to read/write dependencies among transactions" I din't expect to see it, hence there must be something in postgresql theory that I haven't understood well and I'd like a clarification. Most likely one or both transactions have have updated 3 or more tuples on a single page, causing the tuple locks for the transaction on that page to be combined into a single page lock for that transaction. This is intended to prevent the memory required for tracking predicate locks from growing too large. This threshold of 3 per page was entirely arbitrary and always seen as something which could and should be improved someday. That might happen for version 10 (expected to be released next year), since a patch has been submitted to make that configurable. https://www.postgresql.org/message-id/flat/d8joa0eh9yw@dalvik.ping.uio.no#d8joa0eh9yw@dalvik.ping.uio.no If you are able to build from source, you might want to test the efficacy of the patch for your situation. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company Configurable or dynamic? Wouldn't something related to tuples per page (and maybe fillfactor) do the trick? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] update error with serializable
On Fri, Jan 20, 2017 at 11:27 AM, Rob Sargent wrote: > On 01/20/2017 10:05 AM, Kevin Grittner wrote: >> https://www.postgresql.org/message-id/flat/d8joa0eh9yw@dalvik.ping.uio.no#d8joa0eh9yw@dalvik.ping.uio.no > Configurable or dynamic? Wouldn't something related to tuples per page (and > maybe fillfactor) do the trick? Please keep discussion such as that on the thread for the patch. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can we not give tyrannical pedants control of #postgresql?
Hi all! It's really a bad sign when some user is given operator status who is intolerant to minor offtopic conversations that span no more than a couple of line buffers. Witnessing a user getting kicked for asking for my location was way beyond reasonable, considering even the channel was rather idle. Not only that, conversations in the past have elevated to much more heated levels with rare instances of such drastic measures. And they've been great! I've previously considered and advocated #postgresql as the best channel on freenode for years, I'm not sure I can hold this perception from the facts witnessed recently. This particular user and a few others seem to be more inclined to bullying behaviour quick with drastic overractions rather than stable and calm tolerance that was appreciated in the past (I haven't been perfect!). I won't name names, however I've rarely seen one invoke +o privileges in the past and rarely that such a user has shown an immature perspective to databases and intolerant to the perspectives of others that aren't regularly feeding the buffer. I think a little bit of rational consideration on what kind of people would even join #postgresql should be considered and not be so quick to kick due to minor infractions. This leaves one to kick/ban bots and offtopic spammers and severely abusive behaviour. Freenode also has other options beside a +k (+q). I think the operator status of such individuals should be revoked to be honest. However even in regards to database design, server instances and personal real world experience of individuals such as myself, it is unfortunate I won't be able to share that with new users on IRC. I hope that particular stereotypes aren't proven here, but it appears #postgresql encourages a particular tier and makes aware of it's rigid hierarchy. I owe alot to #postgresql but not to these particular users, I've perhaps been idle for too long and the channel has change for the worse, well that's not my fault. I leave it with the community to sort out. All the best, Julian. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why does this hot standy archive_command work
While learning a bit about basic hot standby configuration, I was reviewing an article that used these parameters wal_level = 'hot_standby' archive_mode = on archive_command = 'cd .' max_wal_senders = 1 hot_standby = on How or why that particular archive_command actually works (... and it does ... I tried it ...) is not clear to me based on reading of the Postgresql documentation on this topic. I would have expected to see an actual copy or rsync command, as described in the fine manual at section 25.3.1. "Setting Up WAL Archiving" The entire example appears at https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps Can anyone enlighten on this topic, or provide a link to an existing explanation? -- B -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why does this hot standy archive_command work
> On Jan 20, 2017, at 7:03 PM, bto...@computer.org > wrote: > > While learning a bit about basic hot standby configuration, I was reviewing > an article that used these parameters > > wal_level = 'hot_standby' > archive_mode = on > archive_command = 'cd .' > max_wal_senders = 1 > hot_standby = on > > > How or why that particular archive_command actually works (... and it does > ... I tried it ...) is not clear to me based on reading of the Postgresql > documentation on this topic. I would have expected to see an actual copy or > rsync command, as described in the fine manual at section 25.3.1. "Setting Up > WAL Archiving" > > The entire example appears at > > https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps > > Can anyone enlighten on this topic, or provide a link to an existing > explanation? It's not archiving logs at all, instead relying on streaming them directly to the slave. Changing archive_mode requires a server restart, while changing archive_command from a command that does nothing, successfully, to a command that actually archives logs just requires a reload. So this lets you enable archiving without halting the server by changing the command. Or that's how I vaguely recall it working some years ago. Things may have changed now - you're following a very old tutorial. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
