[GENERAL] PAM implementation in PG 9.2.3
Hi All, I am trying to implement PAM on my local machine. Below are the details of my setup: OS: RHEL 6 PG: 9.2.3 /etc/pam.d/postgresql (PAM file) #%PAM-1.0 authrequiredpam_unix.so account requiredpam_unix.so $PGDATA/pg_hba.conf # TYPE DATABASEUSERADDRESS METHOD # local is for Unix domain socket connections only local all allpam pamservice=postgresql # IPv4 local connections: hostall all 127.0.0.1/32pam pamservice=postgresql Session 1Session 2 -bash-4.1$ psql -U postgres Password for user postgres: -bash-4.1$ tail -f postgresql-2013-04-26_00.log 2013-04-26 20:08:16.605 IST-13943-postgres-postgres-[local] LOG: pam_authenticate failed: Conversation error 2013-04-26 20:08:16.606 IST-13943-postgres-postgres-[local] FATAL: PAM authentication failed for user postgres 2013-04-26 20:08:16.606 IST-13943-postgres-postgres-[local] LOG: could not send data to client: Broken pipe In OS logs(/var/log/secure) Apr 26 20:11:03 localhost res [local] authentication: pam_unix(postgresql:auth): conversation failed Apr 26 20:11:03 localhost res [local] authentication: pam_unix(postgresql:auth): auth could not identify password for [postgres] I have not entered password in session 1 which was waiting for my password to enter, but logs are printed beforehand in both logs (OS/DB) as you see in session 2. When, I enter password in session 1 which was waiting for password, it will immediately takes me into the database without any error. Couple of question's in mind on this scenario: 1. Why database server logs shows lines beforehand as pam_authentication failed even though I have not typed password ? 2. Though logs show as PAM authentication failed, still user's are allowed into the database ? 3. What above log entries mean precisely ? 4. Was my PAM setup successful ? Sorry for my too many questions, I just lost all of my endeavors to understand the above scenario. Any help will be highly appreciated. Thanks in advance. --- Regards, Raghavendra
[GENERAL] Postgresql stuck at open connection
Hi,
In my product in some rare cases , DB connections could not be open, it is
stuck at following location:-
It is stuck at following location :-
sendto(3, p\0\0\0(md5de8bdf202e563b11a4384ba5..., 41, 0, NULL, 0) = 41
0.12rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 0.05poll([{fd=3,
events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}]) 35.027745
regards,S H
[GENERAL] Design advice requested
Apologies for the long email. I would appreciate some advice designing a database which can become very complicated. The purpose of the database is to be a source for bibliometric research. Some background: I work with various sources: * The largest source consists of tagged delimited text files. The database formed from these files takes up about 230Gb at present. I receive weekly updates of new and/or corrected data. I use a python script to parse those files and insert the data into the database. * Other sources are much smaller (up to about 300 000 records in csv-files or Microsoft Access databases) and of various formats. Those sources are also updated from time to time. My aim is to have a database that can easily accommodate the raw data as well as one providing a user friendly structure for researchers using the data. I am working on a multilevel database: * level 0: where the data from the various sources enters. The structure is similar to that of the source. No editing of data other than the updated from the source takes place on this level * level 1: where the data are made available from the sources from level 0 in a common structure which are more suitable for the type of research we are doing, but still separate for each source. Not all the data is important on this level so not all the fields from level 0 are represented here. The data can be added or modified and value added (providing some extra tables for value added data which are source-specific). This level gets populated from level 0 in such a way that value added data don't get overwritten. I found a valuable article for the basic design of this level in an article: Title: A relational database for bibliometric analysis Author(s): Mallig Nicolai Source: JOURNAL OF INFORMETRICS Volume: 4 Issue: 4 Pages: 564-580 DOI: 10.1016/j.joi.2010.06.007 Published: OCT 2010 * level 2: An abstraction layer where queries spanning the various sources are running. This is a read-only layer. I have considered using views for a large part of level 1 but that seems not to be practical: Queries involving many millions of records (there are at present about 43 000 000 articles in the article-table) takes a long time to execute especially when aggregates are involved. I want to conserve space on the one hand and speed up queries on the other hand. Sometimes I think it is not possible to have both. In stead of views a series of tables created from standard queries may speed up the process, I have thought of creating temporary tables that can be populated periodically at times (like each night or every few hours) using the standard queries. Some of the queries will make use of full text searching as they involve large fields like article abstracts. Basically my request is for advice on how to make this database as fast as possible with as few instances of duplicated data while providing both for the updates on level 0 and value added editing on level 1. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: [GENERAL] PAM implementation in PG 9.2.3
Hello Raghavendra, I think probably psql is a problem here. WIthout -W (force password before connect) option psql has no way knowing if this user needs a password to connect to the given database. So, psql first attempts a connection to the database without a password (if -W is not specified), which fails since server responds with password needed kind of message back to psql (this is because we have set 'pam' as the authentication method). This makes psql know that a password is needed for this user/database combination and it prompts for the password and subsequently connects successfully if correct password is specified. But this first unsuccessful attempt is indeed logged by the server as authentication failure just as what you see. So, this logged failure is just the kind of dummy connection attempt (indeed withoutn password) made by the psql. However, if you specify -W option, psql won't connect before it accepts password. You can try this (and see that no authentication failure is logged) I think log entries just mean the authentication has failed with PAM-specific error message. -- Amit Langote -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Does it make sense to break a large query into separate functions?
Greetings, I have a domain specific language implementation that ends up as sql after an automatic transformation/code generation phase. The complexity of the generated sql queries can be significant, with lots of self joins and many other criteria injected into sql. I've just noticed that some parts of the autogenerated queries can be functions on their own. Would moving these parts to their own functions help the query planner? There are any two tables and all queries use them, so I thought the query planner may be able to do better if I let it deal with smaller queries rather than one quite large one. I don't know how the query planner works when there are calls to other functions from a function though. Changing the code generator would take time, so I decided to ask for more educated input than mine. Best regards Seref
[GENERAL] question on most efficient way to increment a column
If I have a simple table with an id as a primary key that is a serial column and a column to keep track of a total_count for a particular id, what method would provide the fastest way to increment the total_count in the shortest amount of time and minimize any locking? id serial total_count integer Best regards, Ty
Re: [GENERAL] question on most efficient way to increment a column
On Wed, May 8, 2013 at 8:45 AM, Tyson Maly tvm...@yahoo.com wrote: If I have a simple table with an id as a primary key that is a serial column and a column to keep track of a total_count for a particular id, what method would provide the fastest way to increment the total_count in the shortest amount of time and minimize any locking? id serial total_count integer uh, update foo set total_count = total_count + 1 where id = x; ? merlin -- 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] question on most efficient way to increment a column
Tyson Maly wrote: If I have a simple table with an id as a primary key that is a serial column and a column to keep track of a total_count for a particular id, what method would provide the fastest way to increment the total_count in the shortest amount of time and minimize any locking? id serial total_count integer UPDATE tablename SET total_count=total_count+1 WHERE id=42; Yours, Laurenz Albe -- 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] Does it make sense to break a large query into separate functions?
Seref Arikan serefari...@kurumsalteknoloji.com writes: I've just noticed that some parts of the autogenerated queries can be functions on their own. Would moving these parts to their own functions help the query planner? It's difficult to answer that without a lot more detail than you've provided, but my offhand guess would be no. Usually it's better when the planner can expand functions inline, which would just be reversing the transformation you're thinking of. 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] Does it make sense to break a large query into separate functions?
Thanks Tom, I am happy with a 10K feet level discussion of the approach, and your response is just what I was looking for. Regards Seref On Wed, May 8, 2013 at 3:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Seref Arikan serefari...@kurumsalteknoloji.com writes: I've just noticed that some parts of the autogenerated queries can be functions on their own. Would moving these parts to their own functions help the query planner? It's difficult to answer that without a lot more detail than you've provided, but my offhand guess would be no. Usually it's better when the planner can expand functions inline, which would just be reversing the transformation you're thinking of. regards, tom lane
Re: [GENERAL] Design advice requested
On 08/05/13 21:21, Johann Spies wrote: Basically my request is for advice on how to make this database as fast as possible with as few instances of duplicated data while providing both for the updates on level 0 and value added editing on level 1. Regards Johann Hi Johann. Firstly, don't worry too much about speed in the design phase, there may be differences of opinion here, but mine is that even with database design the first fundamental layer is the relationship model. That is, regardless of how the raw data is presented to you (CSV, raw text, other relationship models or ideas), the same back to basics problem must be solved - What is the most effective and efficient way of storing this data, that will allow for database flexibility and scalability (future adaptation of new relationship models). Secondly, assuming the CSV and other raw data is in the flat (fat) table format (contains columns of duplicate data). Its your job to determine how to break it down into separate sections (tables) of data and how they relate to each other (normalization). One to many, many to many, etc. There's also other things to consider (i.e data history, revision), but those are the basics. Thirdly, its the queries and the relationships they reveal (joins) between sections of data (tables) that assist in making the data presentable and you can always later on utilize caches for blocks of data that can be in the database itself (temp tables, MV's etc) TIP: whether its temps, views, or materialized views its a good idea to be consistent with the name i.e. some_view. This provides a level of abstraction and is handy in the design phase. It doesn't matter if you are dealing with petabytes of data. Thats all I can suggest without actually looking at a sample of the data (problem) you are dealing with. Its a matter of breaking it down into logical steps and having some fun. Regards, Julian. -- 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] Does it make sense to break a large query into separate functions?
On 08/05/13 23:40, Seref Arikan wrote: Greetings, I have a domain specific language implementation that ends up as sql after an automatic transformation/code generation phase. The complexity of the generated sql queries can be significant, with lots of self joins and many other criteria injected into sql. I've just noticed that some parts of the autogenerated queries can be functions on their own. Would moving these parts to their own functions help the query planner? There are any two tables and all queries use them, so I thought the query planner may be able to do better if I let it deal with smaller queries rather than one quite large one. I don't know how the query planner works when there are calls to other functions from a function though. Changing the code generator would take time, so I decided to ask for more educated input than mine. Best regards Seref Hi Seref, The code generated sql queries isn't giving you much to work with (or a choice). However I suspect its doing its best dealing with this data structure (relationship model). I could be wrong. But that might be where the problem is. Regards, 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] SELECT count(*) differs from result in pgadmin
This is a small, but weird problem. Completely regular table : - requesting count in pgadmin shows 3124448 rows - running SELECT count(*) via the query tool returns 5997620 Why is there a difference? There's nothing remotely remarkable about the table. I've run a full database VACUUM just to be certain I've run VACUUM ANALYZE on that specific table Database version is 9.1 (x64) on windows verbose output from vacuum is -- INFO: vacuuming public.tablename INFO: index tablename_pkey now contains 3124448 row versions in 12233 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.09s/0.00u sec elapsed 0.09 sec. INFO: index tablename_character_id now contains 3124448 row versions in 14898 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.10s/0.00u sec elapsed 0.10 sec. INFO: index tablename_index now contains 3124448 row versions in 14694 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.07s/0.03u sec elapsed 0.10 sec. INFO: index tablename_key_idx now contains 3124448 row versions in 23154 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.12s/0.04u sec elapsed 0.16 sec. INFO: tablename: found 0 removable, 0 nonremovable row versions in 0 out of 104149 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.40s/0.07u sec elapsed 0.50 sec. INFO: analyzing public.tablename INFO: tablename: scanned 3 of 104149 pages, containing 90 live rows and 0 dead rows; 3 rows in sample, 3124454 estimated total rows INFO: analyzing public.tablename inheritance tree INFO: tablename: scanned 15628 of 104149 pages, containing 468840 live rows and 0 dead rows; 15628 rows in sample, 3124451 estimated total rows INFO: archive_tablename: scanned 14372 of 95773 pages, containing 431160 live rows and 0 dead rows; 14372 rows in sample, 2873175 estimated total rows Query returned successfully with no result in 2611 ms. -- ...So it would seem that PGAdmin is correct, but why am I getting the wrong number from SELECT Count(*)?
Re: [GENERAL] Does it make sense to break a large query into separate functions?
On Wed, May 8, 2013 at 9:05 AM, Tom Lane t...@sss.pgh.pa.us wrote: Seref Arikan serefari...@kurumsalteknoloji.com writes: I've just noticed that some parts of the autogenerated queries can be functions on their own. Would moving these parts to their own functions help the query planner? It's difficult to answer that without a lot more detail than you've provided, but my offhand guess would be no. Usually it's better when the planner can expand functions inline, which would just be reversing the transformation you're thinking of. In my experience virtually no useful functions are inlined by the planner. For example, with function: create function f(text) returns date as $$ select to_date($1, ''); $$ language sql stable; /* immutable doesn't help */ I see about 4x time difference between: select to_date(v::text, '') from generate_series(1,10) v; and select f(v::text) from generate_series(1,10) v; I'm curious if more aggressive inlining is a future performance optimization target for postgres or if there is some fundamental restriction that prevents such functions from being inlined. From an abstraction point of view, I'd really like to be able to push more code into functions, but often can't because of performance issues. merlin -- 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] SELECT count(*) differs from result in pgadmin
Oops, got it. Count(*) includes the rowcount of tables that inherit from the table queried? I wasn't counting on that. On Wed, May 8, 2013 at 4:44 PM, Tobias Larsen tobi...@reto.dk wrote: This is a small, but weird problem. Completely regular table : - requesting count in pgadmin shows 3124448 rows - running SELECT count(*) via the query tool returns 5997620 Why is there a difference? There's nothing remotely remarkable about the table. I've run a full database VACUUM just to be certain I've run VACUUM ANALYZE on that specific table Database version is 9.1 (x64) on windows verbose output from vacuum is -- INFO: vacuuming public.tablename INFO: index tablename_pkey now contains 3124448 row versions in 12233 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.09s/0.00u sec elapsed 0.09 sec. INFO: index tablename_character_id now contains 3124448 row versions in 14898 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.10s/0.00u sec elapsed 0.10 sec. INFO: index tablename_index now contains 3124448 row versions in 14694 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.07s/0.03u sec elapsed 0.10 sec. INFO: index tablename_key_idx now contains 3124448 row versions in 23154 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.12s/0.04u sec elapsed 0.16 sec. INFO: tablename: found 0 removable, 0 nonremovable row versions in 0 out of 104149 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.40s/0.07u sec elapsed 0.50 sec. INFO: analyzing public.tablename INFO: tablename: scanned 3 of 104149 pages, containing 90 live rows and 0 dead rows; 3 rows in sample, 3124454 estimated total rows INFO: analyzing public.tablename inheritance tree INFO: tablename: scanned 15628 of 104149 pages, containing 468840 live rows and 0 dead rows; 15628 rows in sample, 3124451 estimated total rows INFO: archive_tablename: scanned 14372 of 95773 pages, containing 431160 live rows and 0 dead rows; 14372 rows in sample, 2873175 estimated total rows Query returned successfully with no result in 2611 ms. -- ...So it would seem that PGAdmin is correct, but why am I getting the wrong number from SELECT Count(*)?
Re: [GENERAL] PAM implementation in PG 9.2.3
On Wed, May 8, 2013 at 4:55 PM, Amit Langote amitlangot...@gmail.comwrote: Hello Raghavendra, I think probably psql is a problem here. WIthout -W (force password before connect) option psql has no way knowing if this user needs a password to connect to the given database. So, psql first attempts a connection to the database without a password (if -W is not specified), which fails since server responds with password needed kind of message back to psql (this is because we have set 'pam' as the authentication method). This makes psql know that a password is needed for this user/database combination and it prompts for the password and subsequently connects successfully if correct password is specified. But this first unsuccessful attempt is indeed logged by the server as authentication failure just as what you see. So, this logged failure is just the kind of dummy connection attempt (indeed withoutn password) made by the psql. Firstly, Thank you for your insight explanation. However, if you specify -W option, psql won't connect before it accepts password. You can try this (and see that no authentication failure is logged) Affirmative, I have tried with -W option and it worked as expected and authentication passed as per PAM setup. However, PG documentation doesn't highlight about this in psql or PAM section, because log entries written are slightly confusing. http://www.postgresql.org/docs/9.2/static/auth-methods.html http://www.postgresql.org/docs/9.2/static/app-psql.html I think log entries just mean the authentication has failed with PAM-specific error message. Yep... understood. --- Regards, Raghavendra Blog: http://raghavt.blogspot.com/ -- Amit Langote
Re: [GENERAL] Does it make sense to break a large query into separate functions?
Merlin Moncure mmonc...@gmail.com writes: In my experience virtually no useful functions are inlined by the planner. For example, with function: create function f(text) returns date as $$ select to_date($1, ''); $$ language sql stable; /* immutable doesn't help */ I see about 4x time difference between: select to_date(v::text, '') from generate_series(1,10) v; and select f(v::text) from generate_series(1,10) v; Something wrong with your measurement technique, because those expand to identical plan trees for me. 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] SELECT count(*) differs from result in pgadmin
Tobias Larsen tobi...@reto.dk writes: Oops, got it. Count(*) includes the rowcount of tables that inherit from the table queried? I wasn't counting on that. To prevent inheriting tables from being scanned as well, run your query as; select count(*) from ONLY footable; On Wed, May 8, 2013 at 4:44 PM, Tobias Larsen tobi...@reto.dk wrote: This is a small, but weird problem. Completely regular table : - requesting count in pgadmin shows 3124448 rows - running SELECT count(*) via the query tool returns 5997620 Why is there a difference? There's nothing remotely remarkable about the table. I've run a full database VACUUM just to be certain I've run VACUUM ANALYZE on that specific table Database version is 9.1 (x64) on windows verbose output from vacuum is? -- INFO: ?vacuuming public.tablename INFO: ?index tablename_pkey now contains 3124448 row versions in 12233 pages DETAIL: ?0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.09s/0.00u sec elapsed 0.09 sec. INFO: ?index tablename_character_id now contains 3124448 row versions in 14898 pages DETAIL: ?0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.10s/0.00u sec elapsed 0.10 sec. INFO: ?index tablename_index now contains 3124448 row versions in 14694 pages DETAIL: ?0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.07s/0.03u sec elapsed 0.10 sec. INFO: ?index tablename_key_idx now contains 3124448 row versions in 23154 pages DETAIL: ?0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.12s/0.04u sec elapsed 0.16 sec. INFO: ?tablename: found 0 removable, 0 nonremovable row versions in 0 out of 104149 pages DETAIL: ?0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.40s/0.07u sec elapsed 0.50 sec. INFO: ?analyzing public.tablename INFO: ?tablename: scanned 3 of 104149 pages, containing 90 live rows and 0 dead rows; 3 rows in sample, 3124454 estimated total rows INFO: ?analyzing public.tablename inheritance tree INFO: ?tablename: scanned 15628 of 104149 pages, containing 468840 live rows and 0 dead rows; 15628 rows in sample, 3124451 estimated total rows INFO: ?archive_tablename: scanned 14372 of 95773 pages, containing 431160 live rows and 0 dead rows; 14372 rows in sample, 2873175 estimated total rows Query returned successfully with no result in 2611 ms. -- ...So it would seem that PGAdmin is correct, but why am I getting the wrong number from SELECT Count(*)? -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] Segmentation fault with core dump
| I'm using PG 9.1.9 with a client application using various versions of
the
| pgsqlODBC driver on Windows. Cursors are used heavily, as well as some
pretty
| heavy trigger queries on db writes which update several materialized
views.
|
| The server has 48GB RAM installed, PG is configured for 12GB shared
buffers,
| 8MB max_stack_depth, 32MB temp_buffers, and 2MB work_mem. Most of the
other
| settings are defaults.
|
| The server will seg fault from every few days to up to two weeks. Each
time
| one of the postgres server processes seg faults, the server gets
terminated by
| signal 11, restarts in recovery for up to 30 seconds, after which time it
| accepts connections as if nothing ever happened. Unfortunately all the
open
| cursors and connections are lost, so the client apps are left in a bad
state.
|
| Seg faults have also occurred with PG 8.4. ... I migrated the database to
a
| server running PG9.1 with the hopes that the problem would disappear, but
it
| has not. So now I'm starting to debug.
|
| # uname -a
| Linux [hostname] 2.6.32-358.2.1.el6.x86_64 #1 SMP Tue Mar 12 14:18:09 CDT
2013
| x86_64 x86_64 x86_64 GNU/Linux
| # cat /etc/redhat-release
| Scientific Linux release 6.3 (Carbon)
|
| # psql -U jberry
| psql (9.1.9)
| Type help for help.
|
| jberry=# select version();
|version
|
---
| PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7
| 20120313 (Red Hat 4.4.7-3), 64-bit
| (1 row)
I've had another postmaster segfault on my production server. It appears to
be the same failure as the last one nearly a month ago, but I wanted to
post the gdb bt details in case it helps shed light on the issue. Please
let me know if anyone would like to drill into the dumped core with greater
detail. Both the OS and PG versions remain unchanged.
Kind Regards,
-Joshua
From the PG log:
2013-05-07 08:48:35 CDT %@ LOG: server process (PID 12367) was
terminated by signal 11: Segmentation fault
From /var/log/messages
May 7 08:48:17 active kernel: postmaster[12367]: segfault at 40 ip
00710e2e sp 7fffdcaeedf0 error 4 in postgres[40+4ea000]
(gdb) bt full
#0 ResourceOwnerEnlargeCatCacheRefs (owner=0x0) at resowner.c:605
newmax = value optimized out
#1 0x006e1382 in SearchCatCache (cache=0x25bf270, v1=value
optimized out, v2=value optimized out, v3=value optimized out,
v4=value optimized out) at catcache.c:1143
res = 1 '\001'
cur_skey = {{sk_flags = 0, sk_attno = -2, sk_strategy = 3,
sk_subtype = 0, sk_collation = 0, sk_func = {fn_addr = 0x686640 oideq,
fn_oid = 184, fn_nargs = 2, fn_strict = 1 '\001',
fn_retset = 0 '\000', fn_stats = 2 '\002', fn_extra = 0x0,
fn_mcxt = 0x252f108, fn_expr = 0x0}, sk_argument = 20}, {sk_flags = 0,
sk_attno = 0, sk_strategy = 0, sk_subtype = 0,
sk_collation = 0, sk_func = {fn_addr = 0, fn_oid = 0, fn_nargs
= 0, fn_strict = 0 '\000', fn_retset = 0 '\000', fn_stats = 0 '\000',
fn_extra = 0x0, fn_mcxt = 0x0, fn_expr = 0x0},
sk_argument = 0}, {sk_flags = 0, sk_attno = 0, sk_strategy = 0,
sk_subtype = 0, sk_collation = 0, sk_func = {fn_addr = 0, fn_oid = 0,
fn_nargs = 0, fn_strict = 0 '\000',
fn_retset = 0 '\000', fn_stats = 0 '\000', fn_extra = 0x0,
fn_mcxt = 0x0, fn_expr = 0x0}, sk_argument = 0}, {sk_flags = 0, sk_attno =
0, sk_strategy = 0, sk_subtype = 0,
sk_collation = 0, sk_func = {fn_addr = 0, fn_oid = 0, fn_nargs
= 0, fn_strict = 0 '\000', fn_retset = 0 '\000', fn_stats = 0 '\000',
fn_extra = 0x0, fn_mcxt = 0x0, fn_expr = 0x0},
sk_argument = 0}}
hashValue = 2280326203
hashIndex = 59
elt = 0x7fe0742cf228
ct = 0x7fe0742cf218
relation = value optimized out
scandesc = value optimized out
ntp = value optimized out
#2 0x006ec69e in getTypeOutputInfo (type=20, typOutput=0x277dea0,
typIsVarlena=0x277dea8 ) at lsyscache.c:2438
typeTuple = value optimized out
pt = value optimized out
__func__ = getTypeOutputInfo
#3 0x00459027 in printtup_prepare_info (myState=0x25d53d0,
typeinfo=0x2686640, numAttrs=84) at printtup.c:263
thisState = value optimized out
format = value optimized out
formats = 0x0
i = value optimized out
__func__ = printtup_prepare_info
#4 0x004593c4 in printtup (slot=0x26fd960, self=0x25d53d0) at
printtup.c:297
typeinfo = value optimized out
myState = 0x25d53d0
buf = {data = 0x262cf40 \320I\\\002, len = 40884576, maxlen = 0,
cursor = 40884576}
natts = 84
i = value optimized out
#5 0x006376ca in RunFromStore (portal=0x262cf40, direction=value
optimized out, count=10, dest=0x25d53d0) at pquery.c:1121
oldcontext = 0x295a470
ok = value optimized out
current_tuple_count = 0
slot =
[GENERAL] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4
I've tried several times to upgrade a test database (with real data, ~500 GB) from 9.1 to 9.2 using pg_upgrade and every time it fails with the same error. I've tried a few different options to pg_upgrade but always the same result. Nothing really useful has turned up in Google. Any thoughts? Complete output is below: -bash-4.1$ time /usr/pgsql-9.2/bin/pg_upgrade -b /usr/pgsql-9.1/bin/ -B /usr/pgsql-9.2/bin/ -d /var/lib/pgsql/9.1/data/ -D /var/lib/pgsql/9.2/data/ -P 50433 --link Performing Consistency Checks - Checking current, bin, and data directories ok Checking cluster versions ok Checking database user is a superuser ok Checking for prepared transactions ok Checking for reg* system OID user data typesok Checking for contrib/isn with bigint-passing mismatch ok Creating catalog dump ok Checking for presence of required libraries ok Checking database user is a superuser ok Checking for prepared transactions ok If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing. Performing Upgrade -- Analyzing all rows in the new cluster ok Freezing all rows on the new clusterok Deleting files from new pg_clog ok Copying old pg_clog to new server ok Setting next transaction ID for new cluster ok Resetting WAL archives ok Setting frozenxid counters in new cluster ok Creating databases in the new cluster ok Adding support functions to new cluster ok Restoring database schema to new clusterok Removing support functions from new cluster ok Adding .old suffix to old global/pg_control ok If you want to start the old cluster, you will need to remove the .old suffix from /var/lib/pgsql/9.1/data/global/pg_control.old. Because link mode was used, the old cluster cannot be safely started once the new cluster has been started. Linking user relation files /var/lib/pgsql/9.1/data/base/16406/3016054 Mismatch of relation OID in database dbname: old OID 2938685, new OID 299721 Failure, exiting real12m31.600s user1m11.594s sys 1m2.519s -- 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] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Evan D. Hoffman Sent: Wednesday, May 08, 2013 2:27 PM To: Postgresql Mailing List Subject: [GENERAL] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4 I've tried several times to upgrade a test database (with real data, ~500 GB) from 9.1 to 9.2 using pg_upgrade and every time it fails with the same error. I've tried a few different options to pg_upgrade but always the same result. Nothing really useful has turned up in Google. Any thoughts? Complete output is below: Linking user relation files /var/lib/pgsql/9.1/data/base/16406/3016054 Mismatch of relation OID in database dbname: old OID 2938685, new OID 299721 Failure, exiting Is it always the same file, same OIDs (old/new)? If it's the same file, did you try to find out what relation it belongs to? Igor Neyman -- 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] Does it make sense to break a large query into separate functions?
On Wed, May 8, 2013 at 11:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: In my experience virtually no useful functions are inlined by the planner. For example, with function: create function f(text) returns date as $$ select to_date($1, ''); $$ language sql stable; /* immutable doesn't help */ I see about 4x time difference between: select to_date(v::text, '') from generate_series(1,10) v; and select f(v::text) from generate_series(1,10) v; Something wrong with your measurement technique, because those expand to identical plan trees for me. you're right! interesting. I had left the function f() as 'immutable' (which is technically incorrect) before running performance test: postgres=# create or replace function f(text) returns date as $$ select to_date($1, ''); $$ language sql stable; CREATE FUNCTION Time: 1.000 ms postgres=# explain analyze select f(v::text) from generate_series(1,10) v; QUERY PLAN Function Scan on generate_series v (cost=0.00..17.50 rows=1000 width=4) (actual time=12.949..110.804 rows=10 loops=1) Total runtime: 167.938 ms (2 rows) Time: 169.017 ms postgres=# create or replace function f(text) returns date as $$ select to_date($1, ''); $$ language sql immutable; CREATE FUNCTION Time: 2.000 ms postgres=# explain analyze select f(v::text) from generate_series(1,10) v; QUERY PLAN - Function Scan on generate_series v (cost=0.00..265.00 rows=1000 width=4) (actual time=15.362..499.792 rows=10 loops=1) Total runtime: 562.465 ms (2 rows) odd that stable function is inlined but immutable isn't! merlin -- 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] Does it make sense to break a large query into separate functions?
Merlin Moncure mmonc...@gmail.com writes: odd that stable function is inlined but immutable isn't! Well, it knows that the expansion to to_date() would only be stable not immutable (because to_date depends on some GUC settings), so doing the expansion could change the behavior, eg by preventing constant-folding. Although usually wrapping a stable function in an immutable one is a recipe for disaster, we don't forbid it because there are cases where it makes sense --- for instance, you might know that the function really is immutable *in your usage*, and want to use it as an index function or some such. But the SQL-function wrapper adds a lot of overhead. I think a plpgsql wrapper would be better here, if you need to cheat about the mutability. 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] Trigger function on Warm Standby
Hi all, I have a Primary Standby setup with streaming replication. Trigger is created on a table, and all it does it to log a message. The trigger works as expected on Primary, however, I don't see the same on standby. I alter the table to have ENABLE ALWAYS TRIGGER, I verified the setting on both Primary and Standby which is set to A. Standby: -bash-4.1$ psql -c select tgrelid, tgname, tgenabled from pg_trigger where tgname='processnodeafter' tgrelid | tgname | tgenabled -+--+--- 19218 | processnodeafter | A Primary: postgres=# select tgrelid, tgname, tgenabled from pg_trigger where tgname='processnodeafter'; tgrelid | tgname | tgenabled -+--+--- 19218 | processnodeafter | A (1 row) Can someone tell me if the trigger will never happen on the Standby server? Is it expected or am I missing some other settings? Thanks~ Ning
Re: [GENERAL] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4
-Original Message- From: Evan D. Hoffman [mailto:evandhoff...@gmail.com] Sent: Wednesday, May 08, 2013 3:35 PM To: Igor Neyman Subject: Re: [GENERAL] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4 Looks like it IS the same OID every time, referencing an index. I already reindexed the entire DB in case it was some problem with a corrupt index. Here's the index info, if it's of any use. Interestingly, if I query which that relation's in, it's not the one that it complained about: db=# select pg_relation_filepath(2938685); pg_relation_filepath -- base/16407/21446253 (1 row) db=# (The file referenced in the error was /var/lib/pgsql/9.1/data/base/16406/3016054) On Wed, May 8, 2013 at 2:35 PM, Igor Neyman iney...@perceptron.com wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Evan D. Hoffman Sent: Wednesday, May 08, 2013 2:27 PM To: Postgresql Mailing List Subject: [GENERAL] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4 Linking user relation files /var/lib/pgsql/9.1/data/base/16406/3016054 Mismatch of relation OID in database dbname: old OID 2938685, new OID 299721 Failure, exiting Is it always the same file, same OIDs (old/new)? If it's the same file, did you try to find out what relation it belongs to? Igor Neyman Is it the same file though? And, if it is what do you get when you run: Select relname from pg_class where relfilenode = 3016054::oid; Please, reply to the list (reply to all), so that other people who may have better ideas/solutions for could see it. Igor Neyman -- 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] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4
Well, each time it fails it refers to the file /var/lib/pgsql/9.1/data/base/16406/3016054, but that's not the file associated with OID 2938685. Here's the output of that query: db=# Select relname from pg_class where relfilenode = 3016054::oid; relname - (0 rows) db=# On Wed, May 8, 2013 at 4:12 PM, Igor Neyman iney...@perceptron.com wrote: -Original Message- From: Evan D. Hoffman [mailto:evandhoff...@gmail.com] Sent: Wednesday, May 08, 2013 3:35 PM To: Igor Neyman Subject: Re: [GENERAL] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4 Looks like it IS the same OID every time, referencing an index. I already reindexed the entire DB in case it was some problem with a corrupt index. Here's the index info, if it's of any use. Interestingly, if I query which that relation's in, it's not the one that it complained about: db=# select pg_relation_filepath(2938685); pg_relation_filepath -- base/16407/21446253 (1 row) db=# (The file referenced in the error was /var/lib/pgsql/9.1/data/base/16406/3016054) On Wed, May 8, 2013 at 2:35 PM, Igor Neyman iney...@perceptron.com wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Evan D. Hoffman Sent: Wednesday, May 08, 2013 2:27 PM To: Postgresql Mailing List Subject: [GENERAL] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4 Linking user relation files /var/lib/pgsql/9.1/data/base/16406/3016054 Mismatch of relation OID in database dbname: old OID 2938685, new OID 299721 Failure, exiting Is it always the same file, same OIDs (old/new)? If it's the same file, did you try to find out what relation it belongs to? Igor Neyman Is it the same file though? And, if it is what do you get when you run: Select relname from pg_class where relfilenode = 3016054::oid; Please, reply to the list (reply to all), so that other people who may have better ideas/solutions for could see it. Igor Neyman -- 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] Trigger function on Warm Standby
ning chan ninchan8...@gmail.com writes: Hi all, I have a Primary Standby setup with streaming replication. Trigger is created on a table, and all it does it to log a message. The trigger works as expected on Primary, however, I don't see the same on standby. I alter the table to have ENABLE ALWAYS TRIGGER, I verified the setting on both Primary and Standby which is set to A. Standby: -bash-4.1$ psql -c select tgrelid, tgname, tgenabled from pg_trigger where tgname='processnodeafter' ?tgrelid |? tgname? | tgenabled -+--+--- ?? 19218 | processnodeafter | A Primary: postgres=# select tgrelid, tgname, tgenabled from pg_trigger where tgname='processnodeafter'; ?tgrelid |? tgname? | tgenabled -+--+--- ?? 19218 | processnodeafter | A (1 row) Can someone tell me if the trigger will never happen on the Standby server? Is it expected or am I missing some other settings? It will not fire on a standby. Thanks~ Ning -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4
Evan D. Hoffman evandhoff...@gmail.com writes: (The file referenced in the error was /var/lib/pgsql/9.1/data/base/16406/3016054) I'm not sure about how pg_upgrade manages its output, but it seems entirely possible that that was the last file successfully transferred, not the one the error occurred on. Looks like it IS the same OID every time, referencing an index. What index exactly? Anything different about that index (or its table) from others in the database? 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] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4
-Original Message- From: Evan D. Hoffman [mailto:evandhoff...@gmail.com] Sent: Wednesday, May 08, 2013 4:22 PM To: Igor Neyman Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4 Well, each time it fails it refers to the file /var/lib/pgsql/9.1/data/base/16406/3016054, but that's not the file associated with OID 2938685. Here's the output of that query: db=# Select relname from pg_class where relfilenode = 3016054::oid; relname - (0 rows) db=# And that is before running pg_upgrade, right? Seems like some kind of pg_catalog corruption. I guess, Bruce Momjian would know better, what's going on here. Igor Neyman -- 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] Does it make sense to break a large query into separate functions?
On 09/05/13 02:47, Merlin Moncure wrote: On Wed, May 8, 2013 at 9:05 AM, Tom Lane t...@sss.pgh.pa.us wrote: Seref Arikan serefari...@kurumsalteknoloji.com writes: I've just noticed that some parts of the autogenerated queries can be functions on their own. Would moving these parts to their own functions help the query planner? It's difficult to answer that without a lot more detail than you've provided, but my offhand guess would be no. Usually it's better when the planner can expand functions inline, which would just be reversing the transformation you're thinking of. In my experience virtually no useful functions are inlined by the planner. For example, with function: create function f(text) returns date as $$ select to_date($1, ''); $$ language sql stable; /* immutable doesn't help */ I see about 4x time difference between: select to_date(v::text, '') from generate_series(1,10) v; and select f(v::text) from generate_series(1,10) v; I'm curious if more aggressive inlining is a future performance optimization target for postgres or if there is some fundamental restriction that prevents such functions from being inlined. From an abstraction point of view, I'd really like to be able to push more code into functions, but often can't because of performance issues. merlin +100 I would very much like to split SQL queries into more manageable parts without loss of performance. I have had SQL queries spanning over a page, and had a sequence of SQL queries with a lot in common. So if I could move the common bits out, it would have made it more maintainable. This was a few years ago in Sybase, but I would have had the same issues in Postgres. I remember having the some complicated SQL queries with multiple sub selects, that had a lot of duplication within the same query, which I would have like to have removed. Common table expressions (the WITH clause) may have helped, but not that much. However, common table expressions would have eliminated the need for some temporary tables, but made for some much longer SQL queries. This was in a stored procedure that was over 3,000 lines long - in SyBase, but I keep thinking how I would have done it in Postgres (I knew of Postgres, but did not have the option to use it). Cheers, Gavin
[GENERAL] pg_upgrade -u
A minor detail in 9.2.4, but I noticed that the pg_upgrade flag for superuser, -u, does not get carried to a -U flag on the vacuumdb commands written to analyze_new_cluster.sh. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] refactoring a database owner without reassign owned
Let's say you have a database which is owned (as well as all the contents within it) by the postgres internal user. Having created or inherited a mess, how do you fix it? database1=# REASSIGN OWNED BY postgres TO foo ; ERROR: cannot reassign ownership of objects owned by role postgres because they are required by the database system Is there some way simpler than going through every object of every type and doing an ALTER TYPE OBJECTNAME OWNER to... ? This is on 9.2.4, but I think it applies to all versions. Cheers, Jeff
Re: [GENERAL] Does it make sense to break a large query into separate functions?
On Wed, May 8, 2013 at 2:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: odd that stable function is inlined but immutable isn't! Well, it knows that the expansion to to_date() would only be stable not immutable (because to_date depends on some GUC settings), so doing the expansion could change the behavior, eg by preventing constant-folding. I see your point-- but you have to admin it's just plain weird -- in this example the behavior is in fact immutable and marking it as such causes it to not be inlined. For purposes of inlining, regardless of the implementation, IMO the function decoration should trump forensic analysis of the function body. Translation: immutable and stable functions should *always* be inlined. More oddness -- when I wrap, say, random() with stable function, I get unique value per returned row, but same value across the set when wrapped with immutable. Although usually wrapping a stable function in an immutable one is a recipe for disaster, we don't forbid it because there are cases where it makes sense --- for instance, you might know that the function really is immutable *in your usage*, and want to use it as an index function or some such. But the SQL-function wrapper adds a lot of overhead. I think a plpgsql wrapper would be better here, if you need to cheat about the mutability. Right. In this case, plpgsql is only about 10% faster than non-inlined sql. inlined sql completely smokes both of them. Regardless, this is a scratch example off of the top of my head. I'm curious if there's a good reference for inlining rules and if their limits have been well explored (and if so, so be it). What I ultimately want is a way to abstract code without using views, dynamic sql, etc. merlin -- 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] Does it make sense to break a large query into separate functions?
On 09/05/13 09:35, Merlin Moncure wrote: [...] More oddness -- when I wrap, say, random() with stable function, I get unique value per returned row, but same value across the set when wrapped with immutable. [..] That part I think I know (but, I'm often wrong!). By saying it is immutable, you are saying that the values returned for the same set of parameters is always the same. The system looks at your immutable function that wraps random() and 'knows' that once invoked, the value returned will always be the same, so why would it want to invoke your immutable function multiple times, as it can just do that once and reuse the value returned? Cheers, Gavin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Normal errors codes in serializable transactions
Hi, The manual mentions that SERIALIZABLE transactions may abort with error 40001, in which case the client application is supposed to retry the transaction. I've been stress testing an application by issuing lots of concurrent requests, and sure enough, every now and then I get back those 40001 errors. However, sometimes I also get back error 40P01. It seems no ill comes to pass if I also retry those transactions, but since this error code is not explicitly mentioned in the manual, one question arises: which error codes can be considered normal (in the sense it's reasonable for the client to retry) when issuing SERIALIZABLE transactions, and which ones (within the scope of class 40, of course) are to be considered real errors? Thanks in advance! Best, Jon -- 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] question on most efficient way to increment a column
On May 8, 2013, at 21:14, Tyson Maly tvm...@yahoo.com wrote: The simple update is one I considered, but I think if I put it into a stored procedure it should run faster Well, you would partially circumvent the query planner, but you would also circumvent any optimisation said query planner would be able to do based on the statistics for the values being updated. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
Re: [GENERAL] Normal errors codes in serializable transactions
The manual mentions that SERIALIZABLE transactions may abort with error 40001, in which case the client application is supposed to retry the transaction. I've been stress testing an application by issuing lots of concurrent requests, and sure enough, every now and then I get back those 40001 errors. However, sometimes I also get back error 40P01. It seems no ill comes to pass if I also retry those transactions, but since this error code is not explicitly mentioned in the manual, one question arises: which error codes can be considered normal (in the sense it's reasonable for the client to retry) when issuing SERIALIZABLE transactions, and which ones (within the scope of class 40, of course) are to be considered real errors? 40P01 is mentioned in the manual. See A. PostgreSQL Error Codes of Appendixes. In most cases it means that transaction is aborted because PostgreSQL detected deadlock. Grepping source indicates that part of HOT standby code uses the error code as well, I'm not sure what is the situation when the error code is supposed to be generated, however. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] pg_upgrade -u
On Wed, May 8, 2013 at 05:05:05PM -0400, Ray Stell wrote: A minor detail in 9.2.4, but I noticed that the pg_upgrade flag for superuser, -u, does not get carried to a -U flag on the vacuumdb commands written to analyze_new_cluster.sh. OK, let me look at this issue. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] question on most efficient way to increment a column
How often are these updated? Once an hour, once a minute, once a second, a thousand times a second? If it's not more than once a second I would look at eager materialized views as a possibility for handing this. http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views#Eager_Materialized_View On Wed, May 8, 2013 at 7:45 AM, Tyson Maly tvm...@yahoo.com wrote: If I have a simple table with an id as a primary key that is a serial column and a column to keep track of a total_count for a particular id, what method would provide the fastest way to increment the total_count in the shortest amount of time and minimize any locking? id serial total_count integer Best regards, Ty -- To understand recursion, one must first understand recursion. -- 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] Normal errors codes in serializable transactions
Jon Smark jon.sm...@yahoo.com wrote: The manual mentions that SERIALIZABLE transactions may abort with error 40001, in which case the client application is supposed to retry the transaction. I've been stress testing an application by issuing lots of concurrent requests, and sure enough, every now and then I get back those 40001 errors. However, sometimes I also get back error 40P01. It seems no ill comes to pass if I also retry those transactions, but since this error code is not explicitly mentioned in the manual, one question arises: which error codes can be considered normal (in the sense it's reasonable for the client to retry) when issuing SERIALIZABLE transactions, and which ones (within the scope of class 40, of course) are to be considered real errors? In PostgreSQL, 40001 is used for serialization failures due to MVCC issues, and 40P01 is used for serialization failures due to deadlocks. I think that many years back when PostgreSQL moved to MVCC it was judged important to differentiate between them with different SQLSTATE values because deadlocks tend to be somewhat more problematic. Had I been involved with PostgreSQL at the time, I would have argued the value of staying with the standard serialization failure SQLSTATE (40001) for both, but it is unlikely to be changed at this point. From the application perspective, both can (and generally should) be treated as meaning that there was nothing wrong with the transaction in itself; it only failed because of conflicts with one or more concurrent transactions and is likely to succeed if retried from the start. These two values are the only ones specifically geared toward dealing with concurrency issues, but it might be worth noting that some constraints (specifically UNIQUE, PRIMARY KEY, FOREIGN KEY, and EXCLUSION) also deal with concurrency issues internally. Those SQLSTATE values aren't something you want to just automatically schedule retries of a transaction for, though; it's just something to keep in mind if an earlier test in a transaction indicated that an operation should be able to succeed and then it fails on the constraint. Such cases normally indicate concurrency issues, not any bug in PostgreSQL or necessarily even in the application. -- Kevin Grittner EnterpriseDB: 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
