[HACKERS] shared_preload_libraries is ignored in single user mode
I found out shared_preload_libraries setting is ignored when we launch postgres in single user mode. In this case, postgres command is launched with --single argument, then the main() directly invokes PostgresMain(); without going through PostmasterMain() which calls process_shared_preload_libraries(). I think we should put the following code block on somewhere within PostgresMain() to fix up it. /* * If not under postmaster, shared preload libraries are not * loaded yet, so we try to load them here. */ if (!IsUnderPostmaster) process_shared_preload_libraries(); The reason why I want to use modules in single user mode is to assign initial security labels on database objects just after initdb. But, the GUC is ignored, we cannot invokes the routines in the module. :( Thanks, -- KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MERGE Specification
On Mon, 2008-04-21 at 21:08 +0100, Simon Riggs wrote:
The following two files specify the behaviour of the MERGE statement and
how it will work in the world of PostgreSQL.
The HTML file was generated from SGML source, though the latter is not
included here for clarity.
Enclose merge.sgml docs for forthcoming MERGE command, as originally
written.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services
!--
$PostgreSQL$
--
refentry id=SQL-MERGE
refmeta
refentrytitle id=SQL-MERGE-TITLEMERGE/refentrytitle
refmiscinfoSQL - Language Statements/refmiscinfo
/refmeta
refnamediv
refnameMERGE/refname
refpurposeupdate, insert or delete rows of a table based upon source data/refpurpose
/refnamediv
indexterm zone=sql-merge
primaryMERGE/primary
/indexterm
refsynopsisdiv
synopsis
MERGE INTO replaceable class=PARAMETERtable/replaceable [ [ AS ] replaceable class=parameteralias/replaceable ]
USING replaceable class=PARAMETERsource-query/replaceable
ON replaceable class=PARAMETERjoin_condition/replaceable
[replaceable class=PARAMETERwhen_clause/replaceable [...]]
where replaceable class=PARAMETERwhen_clause/replaceable is
{ WHEN MATCHED [ AND replaceable class=PARAMETERcondition/replaceable ] THEN { replaceable class=PARAMETERmerge_update/replaceable | DELETE }
WHEN NOT MATCHED [ AND replaceable class=PARAMETERcondition/replaceable ] THEN { replaceable class=PARAMETERmerge_insert/replaceable | DO NOTHING } }
where replaceable class=PARAMETERmerge_update/replaceable is
UPDATE SET { replaceable class=PARAMETERcolumn/replaceable = { replaceable class=PARAMETERexpression/replaceable | DEFAULT } |
( replaceable class=PARAMETERcolumn/replaceable [, ...] ) = ( { replaceable class=PARAMETERexpression/replaceable | DEFAULT } [, ...] ) } [, ...]
and replaceable class=PARAMETERmerge_insert/replaceable is
INSERT [( replaceable class=PARAMETERcolumn/replaceable [, ...] )] { VALUES ( { replaceable class=PARAMETERexpression/replaceable | DEFAULT } [, ...] ) | DEFAULT VALUES }
/synopsis
/refsynopsisdiv
refsect1
titleDescription/title
para
commandMERGE/command performs at most one action on each row from
the target table, driven by the rows from the source query. This
provides a way to specify a single SQL statement that can conditionally
commandUPDATE/command or commandINSERT/command rows, a task
that would otherwise require multiple procedural language statements.
/para
para
First, the commandMERGE/command command performs a left outer join
from source query to target table, producing zero or more merged rows. For
each merged row, literalWHEN/ clauses are evaluated in the
specified order until one of them is activated. The corresponding action
is then applied and processing continues for the next row.
/para
para
commandMERGE/command actions have the same effect as
regular commandUPDATE/command, commandINSERT/command, or
commandDELETE/command commands of the same names, though the syntax
is slightly different.
/para
para
If no literalWHEN/ clause activates then an implicit action of
literalINSERT DEFAULT VALUES/ is performed for that row. If that
implicit action is not desirable an explicit action of
literalDO NOTHING/ may be specified instead.
/para
para
commandMERGE/command will only affect rows only in the specified table.
/para
para
There is no literalRETURNING/ clause with commandMERGE/command.
/para
para
There is no MERGE privilege.
You must have the literalUPDATE/literal privilege on the table
if you specify an update action, the literalINSERT/literal privilege if
you specify an insert action and/or the literalDELETE/literal privilege
if you wish to delete. You will also require the
literalSELECT/literal privilege to any table whose values are read
in the replaceable class=parameterexpressions/replaceable or
replaceable class=parametercondition/replaceable.
/para
/refsect1
refsect1
titleParameters/title
variablelist
varlistentry
termreplaceable class=PARAMETERtable/replaceable/term
listitem
para
The name (optionally schema-qualified) of the table to merge into.
/para
/listitem
/varlistentry
varlistentry
termreplaceable class=parameteralias/replaceable/term
listitem
para
A substitute name for the target table. When an alias is
provided, it completely hides the actual name of the table. For
example, given literalMERGE foo AS f/, the remainder of the
commandMERGE/command statement must refer to this table as
literalf/ not literalfoo/.
/para
/listitem
/varlistentry
varlistentry
termreplaceable class=PARAMETERsource-query/replaceable/term
listitem
para
A query (commandSELECT/command statement or commandVALUES/command
statement) that supplies the rows to be merged into the target
Re: [HACKERS] MERGE Specification
On 05/08/10 10:46, Simon Riggs wrote: On Mon, 2008-04-21 at 21:08 +0100, Simon Riggs wrote: The following two files specify the behaviour of the MERGE statement and how it will work in the world of PostgreSQL. The HTML file was generated from SGML source, though the latter is not included here for clarity. Enclose merge.sgml docs for forthcoming MERGE command, as originally written. Oh, cool, I wasn't aware you had written that already. Boxuan, please include this in your patch, after reviewing and removing/editing anything that doesn't apply to your patch. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] documentation for committing with git
On 04/08/10 16:50, Andrew Dunstan wrote: On 08/04/2010 09:29 AM, Heikki Linnakangas wrote: All those issues can be avoided if you only run git gc when all the working directories are in a clean state, with no staged but uncommitted changes or other funny things. I can live with that gun tied to my ankle ;-). But to make sure of that I think you need to prevent git commands from running gc automatically: git config gc.auto 0 or possibly git config --global gc.auto 0 And you'll need to make sure you run gc yourself from time to time. Good idea. I'll add that to the wiki. I don't like the automatic garbage collection anyway, it always kicks in when I'm doing something, and I end up interrupting it anyway. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] documentation for committing with git
On 05/08/10 05:08, Daniel Farina wrote: On Wed, Aug 4, 2010 at 6:29 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: All those issues can be avoided if you only run git gc when all the working directories are in a clean state, with no staged but uncommitted changes or other funny things. I can live with that gun tied to my ankle ;-). Does even that open a possibility for data loss? Use of the alternates feature will, to my knowledge, never write the referenced repository: all new objects are held in the referencers. The only condition as I understand it is not to generate garbage in the reference repository, and that nominally does not happen in a repo that exists only to be an object pool (you probably even want to use a bare repository instead of one with checked out files). I believe this feature is popular with hosting serving many repos of the same project. The especially paranoid may want to try setting their alternate, referenced repository to be read-only with respect to the user doing all the potentially-modifying work, undoing this if and when they feel like adding more objects to the referenced repository. My guess is one can do a clean checkout and then ride this strategy for quite a long time (a year? more? it depends on how space-conscious one is), so that would not be a incredibly onerous paranoia, if one has it. We're talking about different things again. I was talking about using one shared repository with multiple workdirs created with git-new-workdir. You're talking about anternates. What you say is correct for altrenates, and what I said about staged but not committed changes is correct for the multiple workdirs approach. BTW, git gc has a grace period, so that it won't delete any garbage newer than X days anyway. If I'm reading the git-gc man page correctly, that period is 2 weeks by default. That makes the possibility of accidentally deleting still-interesting staged but not committed changes quite small, even if you run git gc at a wrong time. You wouldn't normally have staged but not committed changes like that lying in backbranches for that long. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tracking inherited columns (was: patch for check constraints using multiple inheritance)
Tom Lane wrote: Yeb Havinga yebhavi...@gmail.com writes: A.a_columnB.a_column | / v v C.a_column C inherits from A and B. Well, if A and B inherited the column from a common ancestor, he can easily do that. If not, maybe he should have thought harder before he started. I do NOT agree that issuing a rename against C is a sane way of dealing with this. Ok, I understand the intuition behind not wanting this kind of update. The root cause seems to center around multiple inheritance of the same column without a common ancestor. Another way to approach the problem, is to prevent the user to create a setup, i.e. when adding a column to B that already exists in A, or when adding a inheritance relation A-C or B-c, if A and B share column names. He could then get a hint he should add a common ancestor with that column. This preemptively prevents problems with renames and other changes. /me ducks regards, Yeb Havinga -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review of Synchronous Replication patches
On Wed, Aug 4, 2010 at 10:00 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Aug 3, 2010 at 1:50 PM, Kolb, Harald (NSN - DE/Munich) harald.k...@nsn.com wrote: Or is fsync still not supported ? Wouldn't you need to have it set to apply to get the behavior you want here? Yes. In that case, replication_mode needs to be set to replay aka apply. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronous replication
On Wed, Aug 4, 2010 at 12:35 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: There's some race conditions with the signaling. If another process finishes XLOG flush and sends the signal when a walsender has just finished one iteration of its main loop, walsender will reset xlogsend_requested and go to sleep. It should not sleep but send the pending WAL immediately. Yep. To avoid that race condition, xlogsend_requested should be reset to false after sleep and before calling XLogSend(). I attached the updated version of the patch. Of course, the code is also available in my git repository: git://git.postgresql.org/git/users/fujii/postgres.git branch: wakeup-walsnd Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center change_poll_loop_in_walsender_0805.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MERGE Specification
On Thu, 2010-08-05 at 12:29 +0300, Heikki Linnakangas wrote: On 05/08/10 10:46, Simon Riggs wrote: On Mon, 2008-04-21 at 21:08 +0100, Simon Riggs wrote: The following two files specify the behaviour of the MERGE statement and how it will work in the world of PostgreSQL. The HTML file was generated from SGML source, though the latter is not included here for clarity. Enclose merge.sgml docs for forthcoming MERGE command, as originally written. Oh, cool, I wasn't aware you had written that already. Boxuan, please include this in your patch, after reviewing and removing/editing anything that doesn't apply to your patch. Also had these fragments as well, if they're still useful. Probably just useful as pointers as to what else to change to include the docs. The tests and docs were written from SQL standard, so any deviations would need to be flagged. The idea of writing the tests first was that they provide an objective test of whether the implementation works according to spec. I'd quite like a commentary on anything that needs changing. Not saying I will necessarily object to differences, but knowing the differences sounds important for us. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services Index: doc/src/sgml/reference.sgml === RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/reference.sgml,v retrieving revision 1.66 diff -c -r1.66 reference.sgml *** doc/src/sgml/reference.sgml 27 Mar 2008 17:24:16 - 1.66 --- doc/src/sgml/reference.sgml 18 Apr 2008 17:50:31 - *** *** 135,140 --- 135,141 listen; load; lock; +merge; move; notify; prepare; Index: doc/src/sgml/ref/allfiles.sgml === RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/ref/allfiles.sgml,v retrieving revision 1.73 diff -c -r1.73 allfiles.sgml *** doc/src/sgml/ref/allfiles.sgml 27 Mar 2008 17:24:16 - 1.73 --- doc/src/sgml/ref/allfiles.sgml 18 Apr 2008 11:10:16 - *** *** 107,112 --- 107,113 !entity listen system listen.sgml !entity load system load.sgml !entity lock system lock.sgml + !entity merge system merge.sgml !entity move system move.sgml !entity notify system notify.sgml !entity preparesystem prepare.sgml Index: doc/src/sgml/ref/update.sgml === RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/ref/update.sgml,v retrieving revision 1.46 diff -c -r1.46 update.sgml *** doc/src/sgml/ref/update.sgml 15 Feb 2008 22:17:06 - 1.46 --- doc/src/sgml/ref/update.sgml 21 Apr 2008 19:01:36 - *** *** 322,327 --- 322,330 -- continue with other operations, and eventually COMMIT; /programlisting + +This operation can be executed in a single statement using +xref linkend=sql-merge endterm=sql-merge-title. /para para -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tracking inherited columns (was: patch for check constraints using multiple inheritance)
On Thu, Aug 5, 2010 at 6:15 AM, Yeb Havinga yebhavi...@gmail.com wrote: Tom Lane wrote: Yeb Havinga yebhavi...@gmail.com writes: A.a_column B.a_column | / v v C.a_column C inherits from A and B. Well, if A and B inherited the column from a common ancestor, he can easily do that. If not, maybe he should have thought harder before he started. I do NOT agree that issuing a rename against C is a sane way of dealing with this. Ok, I understand the intuition behind not wanting this kind of update. The root cause seems to center around multiple inheritance of the same column without a common ancestor. Another way to approach the problem, is to prevent the user to create a setup, i.e. when adding a column to B that already exists in A, or when adding a inheritance relation A-C or B-c, if A and B share column names. He could then get a hint he should add a common ancestor with that column. This preemptively prevents problems with renames and other changes. It also breaks compatibility with previous releases for no particular reason. These cases are all marginal enough that it doesn't really make sense to change historical behavior; I think we should confine our efforts to fixing the bugs. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Online backup cause boot failure, anyone know why?
I want to create a database backup when PG is running, so I call
pg_start_backup(''), scp the data to a backup directory, pg_stop_backup.
Then I reboot PG , PG boot failed with log like unexpected pageaddr X/X in log
file X, segment X, offset X WAL ends before end time of backup dump.
Then I check the failure XLOG file, found the error page contains a pageaddr
8K before it should be, and the failure XLOG record a ONLINE CHECKPONT with 60
bytes in former page, the other 4 bytes missing.
Any one met this before? Please help me!
--
Richard
2010-08-05
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online backup cause boot failure, anyone know why?
PS : I am using PG 8.3.7
--
Richard
2010-08-05
-
发件人：Richard
发送日期：2010-08-05 21:19:27
收件人：pgsql-hackers
抄送：
主题：Online backup cause boot failure, anyone know why?
I want to create a database backup when PG is running, so I call
pg_start_backup(''), scp the data to a backup directory, pg_stop_backup.
Then I reboot PG , PG boot failed with log like unexpected pageaddr X/X in log
file X, segment X, offset X WAL ends before end time of backup dump.
Then I check the failure XLOG file, found the error page contains a pageaddr
8K before it should be, and the failure XLOG record a ONLINE CHECKPONT with 60
bytes in former page, the other 4 bytes missing.
Any one met this before? Please help me!
--
Richard
2010-08-05
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online backup cause boot failure, anyone know why?
On 08/05/2010 09:19 AM, Richard wrote:
I want to create a database backup when PG is running, so I call
pg_start_backup(''), scp the data to a backup directory, pg_stop_backup.
Then I reboot PG , PG boot failed with log like unexpected pageaddr X/X in log file X,
segment X, offset X WAL ends before end time of backup dump.
Then I check the failure XLOG file, found the error page contains a pageaddr
8K before it should be, and the failure XLOG record a ONLINE CHECKPONT with 60
bytes in former page, the other 4 bytes missing.
Any one met this before? Please help me!
This question really belongs on the pgsql-general list, not the -hackers
list.
If all you copied was the data directory then you haven't done this
right anyway. See
http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-TIPS
Why did you reboot postgres after taking your backup?
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Michael Meskes mich...@fam-meskes.de wrote: All prior ECPG versions were fine because dynamic cursor names were only added in 9.0. Apparently only this one place was missed. So this is a bug in the new feature, however not such a major one that it warrants the complete removal IMO. I'd prefer to fix this in 9.0.1. Hope this cleans it up a bit. Thanks. I think I get it now. To restate from another angle, to confirm my understanding: UPDATE WHERE CURRENT OF is working for cursors with the name hard-coded in the embedded statement, which is the only way cursor names were allowed to be specified prior to 9.0; 9.0 implements dynamic cursor names, allowing you to use a variable for the cursor name; but this one use of a cursor name isn't allowing a variable yet. Do I have it right? (If so, I now see why it would be considered a bug.) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Re: [HACKERS] Online backup cause boot failure, anyone know why?
I reboot PG because I found PG recovery end point if far away from the actual
end point of the XLOG on the backup directory, so I want to test if the
original DB is OK.
Unfortunately, I got the same PG log on the original DB. I don't unstand what
you said, I missing what?
--
Richard
2010-08-05
-
发件人：Andrew Dunstan
发送日期：2010-08-05 21:40:13
收件人：Richard
抄送：pgsql-hackers
主题：Re: [HACKERS] Online backup cause boot failure, anyone know why?
On 08/05/2010 09:19 AM, Richard wrote:
I want to create a database backup when PG is running, so I call
pg_start_backup(''), scp the data to a backup directory, pg_stop_backup.
Then I reboot PG , PG boot failed with log like unexpected pageaddr X/X in
log file X, segment X, offset X WAL ends before end time of backup dump.
Then I check the failure XLOG file, found the error page contains a pageaddr
8K before it should be, and the failure XLOG record a ONLINE CHECKPONT with
60 bytes in former page, the other 4 bytes missing.
Any one met this before? Please help me!
This question really belongs on the pgsql-general list, not the -hackers
list.
If all you copied was the data directory then you haven't done this
right anyway. See
http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-TIPS
Why did you reboot postgres after taking your backup?
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MERGE Specification
On Thu, Aug 5, 2010 at 7:25 PM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-08-05 at 12:29 +0300, Heikki Linnakangas wrote: On 05/08/10 10:46, Simon Riggs wrote: On Mon, 2008-04-21 at 21:08 +0100, Simon Riggs wrote: The following two files specify the behaviour of the MERGE statement and how it will work in the world of PostgreSQL. The HTML file was generated from SGML source, though the latter is not included here for clarity. Enclose merge.sgml docs for forthcoming MERGE command, as originally written. Oh, cool, I wasn't aware you had written that already. Boxuan, please include this in your patch, after reviewing and removing/editing anything that doesn't apply to your patch. Thanks a lot for the instruction file of MERGE command. I have read through it carefully. It is really a great work. I have to admit that I am not familiar with the sgml language, and I cannot write the instruction by myself. All features of MERGE demonstrated in this file are consistent with my implementation, EXCEPT the DO NOTHING option. In current edition, we don't have the DO NOTHING action type. That is, during the execution of MERGE commands, if one tuple is not caught by any of the merge actions, it will be ignored. In another word, DO NOTING (although cannot be specified explicitly by user) is the DEFAULT action for tuples. In the contrary, Simon's instruction says that the DEFAULT action for the tuple caught by no actions is WHEN NOT MATCHED THEN INSERT DEFAULT VALUES From the user's point of view, these two kinds of MERGE command may have not much differences. But, as the coder, I prefer current setting, because we can save the implementation for a new type of MERGE actions (DO NOTHING is a special merge action type). And, thus, no checks and special process for it. (For example, we need to make sure that DO NOTHING is the last WHEN clause, and it has no additional qual. And we have to generate a INSERT DEFAULT VALUES action for the MERGE command if we don't find the DO NOTHING action) Well, if people want the DO NOTHING action, I will add it in the system. Now, I have changed the RULE strategy of MERGE to the better logic. And I am working on triggers for MERGE, which is also mentioned in the instruction file. I will build a new patch with no long comment and blank line around functions, and possibly contain the regress test file and this sgml instructions in it. I wish we can reach a agreement on the DO NOTHING thing before my next submission, so I can make necessary modification on my code for it. (the new patch may be finished in one or two days, I think) Thanks! PS: I have an embarrassing question: how to view the sgml instructions of postgres in web page form, rather than read the source code of them? Also had these fragments as well, if they're still useful. Probably just useful as pointers as to what else to change to include the docs. The tests and docs were written from SQL standard, so any deviations would need to be flagged. The idea of writing the tests first was that they provide an objective test of whether the implementation works according to spec. I'd quite like a commentary on anything that needs changing. Not saying I will necessarily object to differences, but knowing the differences sounds important for us. -- Simon Riggs www.2ndQuadrant.com http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
[HACKERS] pg_stat_user_functions' notion of user
pg_stat_user_functions has an inconsistent notion of what user is. Whereas the other pg_stat_user_* views filter out non-user objects by schema, pg_stat_user_functions checks for language internal, which does not successfully exclude builtin functions of language SQL. Is there a reason for this inconsistency? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tracking inherited columns (was: patch for check constraints using multiple inheritance)
Robert Haas robertmh...@gmail.com writes: Yeb Havinga yebhavi...@gmail.com writes: The root cause seems to center around multiple inheritance of the same column without a common ancestor. Another way to approach the problem, is to prevent the user to create a setup, i.e. when adding a column to B that already exists in A, or when adding a inheritance relation A-C or B-c, if A and B share column names. He could then get a hint he should add a common ancestor with that column. This preemptively prevents problems with renames and other changes. It also breaks compatibility with previous releases for no particular reason. Well, if it were only a hint, and thus didn't actually prevent anything, then it wouldn't be breaking compatibility. But I don't like the idea much either. It would be extremely expensive, if not impossible, to determine whether all parents having the similarly-named column got it from the same common ancestor. (In particular, if the user had previously ignored the hint, you could have situations where there isn't a unique ancestor that the column can be traced to; then what do you do?) I think we'd be putting huge amounts of effort into a case that no more than one or two people would ever hit. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online backup cause boot failure, anyone know why?
Richard husttrip...@vip.sina.com writes: PS : I am using PG 8.3.7 I believe there's a related bug fix in 8.3.8. BTW, -hackers is not the place for this type of question. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Re: [HACKERS] Online backup cause boot failure, anyone know why?
On Thu, Aug 5, 2010 at 9:50 AM, Richard husttrip...@vip.sina.com wrote: I reboot PG because I found PG recovery end point if far away from the actual end point of the XLOG on the backup directory, so I want to test if the original DB is OK. Unfortunately, I got the same PG log on the original DB. I don't unstand what you said, I missing what? The transaction logs archived during the backup? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TwoPO: experimental join order algorithm
On Fri, Jul 30, 2010 at 7:02 AM, Jan Urbański wulc...@wulczer.org wrote: On 24/07/10 15:20, Adriano Lange wrote: Hi, Hi! I'd like to release the last version of my experimental join order algorithm (TwoPO - Two Phase Optimization [1]): http://git.c3sl.ufpr.br/gitweb?p=lbd/ljqo.git;a=summary This algorithm is not production-ready, but an experimental set of ideas, which need to be refined and evaluated. As the join order optimization is a hard problem, the evaluation of a search strategy is also a hard task. Therefore, I think the most important TODO item related to replacement of GEQO algorithm is to define a set of evaluation criteria considered as relevant. Good to hear from you -- I don't know if you are aware about a simulated annealing join search module that I'm working on. When I first started, I planned to base is on the patch that you sent to -hackers some time ago (and actually, if you look at the repo for my module, twopo.c is still in there) but ended up doing it from scratch. However, reading your code was a big help in the beginning. I gave a talk at this year's PGCon (http://www.pgcon.org/2010/schedule/events/211.en.html) and you will find your name in the acknowledgments section of the presentation :) I'll make sure to read your new code and compare the approaches, my results so far are not perfect, but also not very pessimistic. I think there is actually a chance to replace GEQO with SA in the future, or at least to ship more join search modules with the standard distribution and gather field reports. Cheers, Jan Hi Jan, It's good to know that you are also interested about this issue! I saw the slides of your presentation at PGCon and I noted excellent ideas. I think that as more as implementations and ideas will be raised, more easily will be to converge them in a reliable and competitive solution. The current version of TwoPO is suitable for the classic select-project-join problem. Therefore, there are some issues not covered yet, as I have observed in a test schema presented by Andres Freund (http://archives.postgresql.org/pgsql-hackers/2009-07/msg00546.php). I think we will need to build a robust benchmark to deal with it. At this moment I'm really busy with a homework, but I hope to dedicate more time to this issue next month. -- Adriano Lange -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tracking inherited columns (was: patch for check constraints using multiple inheritance)
On Thu, Aug 5, 2010 at 9:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: Well, if it were only a hint, and thus didn't actually prevent anything, then it wouldn't be breaking compatibility. But I don't like the idea much either. It would be extremely expensive, if not impossible, to determine whether all parents having the similarly-named column got it from the same common ancestor. (In particular, if the user had previously ignored the hint, you could have situations where there isn't a unique ancestor that the column can be traced to; then what do you do?) I think we'd be putting huge amounts of effort into a case that no more than one or two people would ever hit. I don't agree that it would be a huge amount of effort, but I do agree that only a very small number of people will ever hit it, and that it just doesn't seem worth it. We have bigger fish to fry. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online backup cause boot failure, anyone know why?
Thanks for replying. But I could not find any relation between the RequestXLogSwitch function and the error I met. For perfromance purpose , I change the pg_start_backup checkpoint type from CHECKPOINT_WAIT to CHECKPOINT_IMMEDIATE, does it matter? -- Richard 2010-08-05 - 发件人：Tom Lane 发送日期：2010-08-05 22:04:30 收件人：Richard 抄送：pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? Richard husttrip...@vip.sina.com writes: PS : I am using PG 8.3.7 I believe there's a related bug fix in 8.3.8. BTW, -hackers is not the place for this type of question. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stat_user_functions' notion of user
On Thu, Aug 05, 2010 at 04:58:32PM +0300, Peter Eisentraut wrote: pg_stat_user_functions has an inconsistent notion of what user is. Whereas the other pg_stat_user_* views filter out non-user objects by schema, pg_stat_user_functions checks for language internal, which does not successfully exclude builtin functions of language SQL. Is there a reason for this inconsistency? If I had to hazard a guess, it would be that the functionality was written over time by different people, not all of whom were using the same criteria for coherence. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronous replication
On Wed, Aug 4, 2010 at 10:38 PM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
Then you risk running out of disk space. Similar to having an archive
command that fails for some reason.
That's one reason the registration should not be too automatic - there is
serious repercussions if the standby just disappears. If the standby is a
synchronous one, the master will stop committing or delay acknowledging
commits, depending on the configuration, and the master needs to keep extra
WAL around.
Umm... in addition to registration of each standby, I think we should allow
users to set the upper limit of the number of WAL files kept in pg_xlog to
avoid running out of disk space. If it exceeds the upper limit, the master
disconnects too old standbys from the cluster and removes all the WAL files
not required for current connected standbys. If you don't want any standby
to disappear unexpectedly because of the upper limit, you can set it to 0
(= no limit).
I'm thinking to make users register and unregister each standbys via SQL
functions like register_standby() and unregister_standby():
void register_standby(standby_name text, streaming_start_lsn text)
void unregister_standby(standby_name text)
Note that standby_name should be specified in recovery.conf of each
standby.
By using them we can easily specify which WAL files are unremovable because
of new standby when taking the base backup for it as follows:
SELECT register_standby('foo', pg_start_backup())
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Re: [HACKERS] Re: Re: [HACKERS] Online backup cause boot failure,anyone know why?
Oh sorry, I missed something. I turned off the XLOG archive in code after pg_start_backup so the pg_xlog directory contains all the xlog files. And for performance purpose, I change the checkpoint type in pg_start_backup to CHECKPOINT_IMMEDIATE, does it matter? The PG log I mentioned above is the running error log not the XLOG. -- Richard 2010-08-05 - 发件人：Robert Haas 发送日期：2010-08-05 22:07:45 收件人：Richard 抄送：Andrew Dunstan; pgsql-hackers 主题：Re: [HACKERS] Re: Re: [HACKERS] Online backup cause boot failure,anyone know why? On Thu, Aug 5, 2010 at 9:50 AM, Richard husttrip...@vip.sina.com wrote: I reboot PG because I found PG recovery end point if far away from the actual end point of the XLOG on the backup directory, so I want to test if the original DB is OK. Unfortunately, I got the same PG log on the original DB. I don't unstand what you said, I missing what? The transaction logs archived during the backup? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MERGE Specification
On Thu, Aug 05, 2010 at 09:55:29PM +0800, Boxuan Zhai wrote: On Thu, Aug 5, 2010 at 7:25 PM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-08-05 at 12:29 +0300, Heikki Linnakangas wrote: On 05/08/10 10:46, Simon Riggs wrote: On Mon, 2008-04-21 at 21:08 +0100, Simon Riggs wrote: The following two files specify the behaviour of the MERGE statement and how it will work in the world of PostgreSQL. The HTML file was generated from SGML source, though the latter is not included here for clarity. Enclose merge.sgml docs for forthcoming MERGE command, as originally written. Oh, cool, I wasn't aware you had written that already. Boxuan, please include this in your patch, after reviewing and removing/editing anything that doesn't apply to your patch. Thanks a lot for the instruction file of MERGE command. I have read through it carefully. It is really a great work. I have to admit that I am not familiar with the sgml language, and I cannot write the instruction by myself. It's really not super complicated. It's quite like (and ancestral to) HTML. All features of MERGE demonstrated in this file are consistent with my implementation, EXCEPT the DO NOTHING option. In current edition, we don't have the DO NOTHING action type. That is, during the execution of MERGE commands, if one tuple is not caught by any of the merge actions, it will be ignored. In another word, DO NOTING (although cannot be specified explicitly by user) is the DEFAULT action for tuples. In the contrary, Simon's instruction says that the DEFAULT action for the tuple caught by no actions is WHEN NOT MATCHED THEN INSERT DEFAULT VALUES I believe that the SQL standard specifies this behavior, and I don't think we have a compelling reason to do something different from what the SQL standard specifies. Well, if people want the DO NOTHING action, I will add it in the system. That'd be great :) Now, I have changed the RULE strategy of MERGE to the better logic. And I am working on triggers for MERGE, which is also mentioned in the instruction file. I will build a new patch with no long comment and blank line around functions, and possibly contain the regress test file and this sgml instructions in it. I wish we can reach a agreement on the DO NOTHING thing before my next submission, so I can make necessary modification on my code for it. (the new patch may be finished in one or two days, I think) Thanks! PS: I have an embarrassing question: how to view the sgml instructions of postgres in web page form, rather than read the source code of them? After you've built postgresql, do this: cd doc/src/sgml make Then you can point a web browser at the doc/src/sgml/html/index.html (and similar) http://www.postgresql.org/docs/current/static/docguide.html has information about the tools you will need for the above to work. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online backup cause boot failure, anyone know why?
Thanks for replying. But I could't find relation between the RequestXLogSwitch function and the error I met. For perfromance purpose , I change the pg_start_backup checkpoint type from CHECKPOINT_WAIT to CHECKPOINT_IMMEDIATE, does it matter? -- Richard 2010-08-05 - 发件人：Tom Lane 发送日期：2010-08-05 22:04:30 收件人：Richard 抄送：pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? Richard husttrip...@vip.sina.com writes: PS : I am using PG 8.3.7 I believe there's a related bug fix in 8.3.8. BTW, -hackers is not the place for this type of question. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MERGE Specification
On Thu, 2010-08-05 at 21:55 +0800, Boxuan Zhai wrote: In the contrary, Simon's instruction says that the DEFAULT action for the tuple caught by no actions is WHEN NOT MATCHED THEN INSERT DEFAULT VALUES From the user's point of view, these two kinds of MERGE command may have not much differences. But, as the coder, I prefer current setting, because we can save the implementation for a new type of MERGE actions (DO NOTHING is a special merge action type). And, thus, no checks and special process for it. (For example, we need to make sure that DO NOTHING is the last WHEN clause, and it has no additional qual. And we have to generate a INSERT DEFAULT VALUES action for the MERGE command if we don't find the DO NOTHING action) Well, if people want the DO NOTHING action, I will add it in the system. This is only important when using AND search condition, so its not important for the common UPSERT case of unconditional UPDATE/INSERT. Personally, I would prefer the default action to be RAISE ERROR or similar. Otherwise its just too easy to get complex logic wrong and lose a few rows without noticing. If that was the case then you would definitely need DO NOTHING when you explicitly wanted to lose a few rows. You may think that's a bit strong, but consider that PostgreSQL uses default = ERROR in vast majority of switch() statements. I think its a safe coding practice and the annoyance of having run-time errors is much better than losing rows. The INSERT DEFAULT VALUES was behaviour taken from another DBMS, its not part of the standard AFAICS. Now, I have changed the RULE strategy of MERGE to the better logic. And I am working on triggers for MERGE, which is also mentioned in the instruction file. I will build a new patch with no long comment and blank line around functions, and possibly contain the regress test file and this sgml instructions in it. I wish we can reach a agreement on the DO NOTHING thing before my next submission, so I can make necessary modification on my code for it. (the new patch may be finished in one or two days, I think) Thanks! PS: I have an embarrassing question: how to view the sgml instructions of postgres in web page form, rather than read the source code of them? If you edit the files, as shown in the patches here, then you just need to drop into the doc/sgml/src directory and type make. The SGML will then be compiled into HTML and you can view the resulting file directly in your web browser. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Re: [HACKERS] Online backup cause boot failure,anyone know why?
On Thu, Aug 5, 2010 at 10:20 AM, Richard husttrip...@vip.sina.com wrote: Oh sorry, I missed something. I turned off the XLOG archive in code after pg_start_backup so the pg_xlog directory contains all the xlog files. And for performance purpose, I change the checkpoint type in pg_start_backup to CHECKPOINT_IMMEDIATE, does it matter? The PG log I mentioned above is the running error log not the XLOG. Well, it's pretty clear that you're missing some WAL; otherwise, you wouldn't be getting an error that says WAL ends before end time of backup dump. It's hard to speculate as to whether that's a configuration problem or a result of your custom modifications to the source code, since you haven't provided many details about either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Kevin Grittner írta: Michael Meskes mich...@fam-meskes.de wrote: All prior ECPG versions were fine because dynamic cursor names were only added in 9.0. Apparently only this one place was missed. So this is a bug in the new feature, however not such a major one that it warrants the complete removal IMO. I'd prefer to fix this in 9.0.1. As we are so late in the beta phase, we can live with that, hopefully you will find time by then to review the patch which is actually not that complex, only a bit large. The part of ECPGdo() that deals with auto-preparing statements is moved closer to calling ecpg_execute(), after the varargs are converted to stmt-inlist and -outlist. Hope this cleans it up a bit. Thanks. I think I get it now. To restate from another angle, to confirm my understanding: UPDATE WHERE CURRENT OF is working for cursors with the name hard-coded in the embedded statement, which is the only way cursor names were allowed to be specified prior to 9.0; 9.0 implements dynamic cursor names, allowing you to use a variable for the cursor name; but this one use of a cursor name isn't allowing a variable yet. Do I have it right? (If so, I now see why it would be considered a bug.) Yes, you understand it correctly. Best regards, Zoltán Böszörményi -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online backup cause boot failure, anyone know why?
Richard husttrip...@vip.sina.com writes: For perfromance purpose , I change the pg_start_backup checkpoint type from CHECKPOINT_WAIT to CHECKPOINT_IMMEDIATE, does it matter? Oh, so this isn't so much 8.3.7 as randomly-hacked-up 8.3.7. Yes, that'd break it, I believe. CHECKPOINT_IMMEDIATE doesn't imply waiting. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Re: Re: [HACKERS] Re: Re: [HACKERS] Online backup cause bootfailure,anyone know why?
Thanks for your patience. I use XLogCtl-Insert.forcePageWrites for XLOG recycling flag. So after pg_start_backup, no more XLOG files will be recycled. And as I said above, I make a CHECKPOINT_IMMEDIATE checkpoint in pg_start_backup, instead CHECKPOINT_WAIT. That all I did to code. I wonder whether the XLOG is corrupted, because the first error is unexpected pageaddr %X/%X in log file %u, segment %u, offset %u .The error page addr contains a LSN 8K before it should do and I compare the two pages , they are almost the same except the last several bytes. So it should not be missing some XLOG, can be the XLOG file or buffer was corrupted. -- Richard 2010-08-05 - 发件人：Robert Haas 发送日期：2010-08-05 22:38:37 收件人：Richard 抄送：Andrew Dunstan; pgsql-hackers 主题：Re: Re: [HACKERS] Re: Re: [HACKERS] Online backup cause bootfailure,anyone know why? On Thu, Aug 5, 2010 at 10:20 AM, Richard husttrip...@vip.sina.com wrote: Oh sorry, I missed something. I turned off the XLOG archive in code after pg_start_backup so the pg_xlog directory contains all the xlog files. And for performance purpose, I change the checkpoint type in pg_start_backup to CHECKPOINT_IMMEDIATE, does it matter? The PG log I mentioned above is the running error log not the XLOG. Well, it's pretty clear that you're missing some WAL; otherwise, you wouldn't be getting an error that says WAL ends before end time of backup dump. It's hard to speculate as to whether that's a configuration problem or a result of your custom modifications to the source code, since you haven't provided many details about either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online backup cause boot failure, anyone know why?
I am sorry, my English is poor. I was confused by what you said. What do you mean by saying that'd break it! -- Richard 2010-08-05 - 发件人：Tom Lane 发送日期：2010-08-05 22:44:50 收件人：Richard 抄送：pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? Richard husttrip...@vip.sina.com writes: For perfromance purpose , I change the pg_start_backup checkpoint type from CHECKPOINT_WAIT to CHECKPOINT_IMMEDIATE, does it matter? Oh, so this isn't so much 8.3.7 as randomly-hacked-up 8.3.7. Yes, that'd break it, I believe. CHECKPOINT_IMMEDIATE doesn't imply waiting. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online backup cause boot failure, anyone know why?
I am sorry, my English is poor. I was confused by what you said. What do you mean by saying that'd break it! -- Richard 2010-08-05 - 发件人：Tom Lane 发送日期：2010-08-05 22:44:50 收件人：Richard 抄送：pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? Richard husttrip...@vip.sina.com writes: For perfromance purpose , I change the pg_start_backup checkpoint type from CHECKPOINT_WAIT to CHECKPOINT_IMMEDIATE, does it matter? Oh, so this isn't so much 8.3.7 as randomly-hacked-up 8.3.7. Yes, that'd break it, I believe. CHECKPOINT_IMMEDIATE doesn't imply waiting. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GROUPING SETS revisited
On Thu, Aug 05, 2010 at 04:46:51PM +0200, Pavel Stehule wrote: So Joshua, can you look on code? Sure... thanks :) -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] MERGE Specification
On 05/08/10 17:22, Simon Riggs wrote: On Thu, 2010-08-05 at 21:55 +0800, Boxuan Zhai wrote: In the contrary, Simon's instruction says that the DEFAULT action for the tuple caught by no actions is WHEN NOT MATCHED THEN INSERT DEFAULT VALUES From the user's point of view, these two kinds of MERGE command may have not much differences. But, as the coder, I prefer current setting, because we can save the implementation for a new type of MERGE actions (DO NOTHING is a special merge action type). And, thus, no checks and special process for it. (For example, we need to make sure that DO NOTHING is the last WHEN clause, and it has no additional qual. And we have to generate a INSERT DEFAULT VALUES action for the MERGE command if we don't find the DO NOTHING action) Well, if people want the DO NOTHING action, I will add it in the system. This is only important when using ANDsearch condition, so its not important for the common UPSERT case of unconditional UPDATE/INSERT. Assuming the default action if no other action matches is to do nothing, then an explicit DO NOTHING is just a convenience. You can have the same effect by having an AND NOT condition to all the actions following the DO NOTHING action. I admit it's quite handy, but let's avoid PostgreSQL extensions at this point. Personally, I would prefer the default action to be RAISE ERROR or similar. Otherwise its just too easy to get complex logic wrong and lose a few rows without noticing. If that was the case then you would definitely need DO NOTHING when you explicitly wanted to lose a few rows. You may think that's a bit strong, but consider that PostgreSQL uses default = ERROR in vast majority of switch() statements. I think its a safe coding practice and the annoyance of having run-time errors is much better than losing rows. The INSERT DEFAULT VALUES was behaviour taken from another DBMS, its not part of the standard AFAICS. What does the standard say about this? We should follow the standard, I don't see enough reason to deviate here. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online backup cause boot failure, anyone know why?
On 05/08/10 17:56, Richard wrote: I am sorry, my English is poor. I was confused by what you said. What do you mean by saying that'd break it! Replacing CHECKPOINT_WAIT with CHECKPOINT_IMMEDIATE broke it. Don't do that. If you want to change the behavior of pg_start_backup() to perform the checkpoint immediately, change CHECKPOINT_WAIT to CHECKPOINT_WAIT | CHECKPOINT_IMMEDIATE. The usual work-around though is not to hack the source code, but perform a manual CHECKPOINT just before calling pg_start_backuo(). That makes the checkpoint performed by pg_start_backup() finish quickly. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GROUPING SETS revisited
I found other issue :( postgres=# select name, place from cars group by grouping sets(name, place,()); name | place ---+ bmw | skoda | opel | | germany | czech rep. skoda | czech rep. skoda | germany bmw | czech rep. bmw | germany opel | czech rep. opel | germany (11 rows) postgres=# explain select name, place from cars group by grouping sets(name, place,()); QUERY PLAN -- Append (cost=36.98..88.55 rows=1230 width=54) CTE GroupingSets - Seq Scan on cars (cost=0.00..18.30 rows=830 width=68) - HashAggregate (cost=18.68..20.68 rows=200 width=32) - CTE Scan on GroupingSets (cost=0.00..16.60 rows=830 width=32) - HashAggregate (cost=18.68..20.68 rows=200 width=32) - CTE Scan on GroupingSets (cost=0.00..16.60 rows=830 width=32) - CTE Scan on GroupingSets (cost=0.00..16.60 rows=830 width=64) (8 rows) the combination of nonagregates and empty sets do a problems - because we can't ensure agg mode without aggregates or group by. But it is only minor issue 2010/8/5 Joshua Tolley eggyk...@gmail.com: On Thu, Aug 05, 2010 at 04:46:51PM +0200, Pavel Stehule wrote: So Joshua, can you look on code? Sure... thanks :) -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkxa1NsACgkQRiRfCGf1UMPwzQCgjz52P86Yx4ac4aRkKwjn8OHK 6/EAoJ/CjXEyPaLpx39SI5bKQPz+AwBR =Mi2J -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MERGE Specification
On Thu, 2010-08-05 at 18:17 +0300, Heikki Linnakangas wrote: On 05/08/10 17:22, Simon Riggs wrote: On Thu, 2010-08-05 at 21:55 +0800, Boxuan Zhai wrote: In the contrary, Simon's instruction says that the DEFAULT action for the tuple caught by no actions is WHEN NOT MATCHED THEN INSERT DEFAULT VALUES From the user's point of view, these two kinds of MERGE command may have not much differences. But, as the coder, I prefer current setting, because we can save the implementation for a new type of MERGE actions (DO NOTHING is a special merge action type). And, thus, no checks and special process for it. (For example, we need to make sure that DO NOTHING is the last WHEN clause, and it has no additional qual. And we have to generate a INSERT DEFAULT VALUES action for the MERGE command if we don't find the DO NOTHING action) Well, if people want the DO NOTHING action, I will add it in the system. This is only important when using ANDsearch condition, so its not important for the common UPSERT case of unconditional UPDATE/INSERT. Assuming the default action if no other action matches is to do nothing, then an explicit DO NOTHING is just a convenience. You can have the same effect by having an AND NOT condition to all the actions following the DO NOTHING action. I admit it's quite handy, but let's avoid PostgreSQL extensions at this point. err... * DELETE is an extension to the standard, though supported by Oracle, DB2 and SQLServer and damn useful * INSERT DEFAULT VALUES is an extension to the standard, though matches options on the normal INSERT clause * rule support is an extension to the standard * It appears we would be in violation of the standard on 14.12 General Rule 6 a) i) 2) B), p.890 (Oh, I wish I was joking, there really is such a paragraph number) which specifies that the join between source and target table must not return multiple rows or must return cardinality violation. That's pretty difficult thing to check and not very useful if it does do that. anyway, that list isn't an argument in favour of change. The argument in favour of a fail-safe default is that it is a safe coding practice that the PostgreSQL project already uses itself. The only way to write a safe MERGE SQL statement is with an extension to the standard... Principle of minimal extension would mean we only need to support RAISE ERROR, to allow people to specify they actively want statement to fail if the list of WHEN clauses does not produce a match. Personally, I would prefer the default action to be RAISE ERROR or similar. Otherwise its just too easy to get complex logic wrong and lose a few rows without noticing. If that was the case then you would definitely need DO NOTHING when you explicitly wanted to lose a few rows. You may think that's a bit strong, but consider that PostgreSQL uses default = ERROR in vast majority of switch() statements. I think its a safe coding practice and the annoyance of having run-time errors is much better than losing rows. The INSERT DEFAULT VALUES was behaviour taken from another DBMS, its not part of the standard AFAICS. What does the standard say about this? We should follow the standard, I don't see enough reason to deviate here. I checked the standard before commenting previously and have done so again here. I can't see anything that refers to this (in SQL:2008), either way. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online backup cause boot failure, anyone know why?
All jods are done by client code, not manually. I still did't not understand what you said. What break what? Thandks! -- Richard 2010-08-05 - 发件人：Heikki Linnakangas 发送日期：2010-08-05 23:21:54 收件人：Richard 抄送：Tom Lane; pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? On 05/08/10 17:56, Richard wrote: I am sorry, my English is poor. I was confused by what you said. What do you mean by saying that'd break it! Replacing CHECKPOINT_WAIT with CHECKPOINT_IMMEDIATE broke it. Don't do that. If you want to change the behavior of pg_start_backup() to perform the checkpoint immediately, change CHECKPOINT_WAIT to CHECKPOINT_WAIT | CHECKPOINT_IMMEDIATE. The usual work-around though is not to hack the source code, but perform a manual CHECKPOINT just before calling pg_start_backuo(). That makes the checkpoint performed by pg_start_backup() finish quickly. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MERGE Specification
On Thu, Aug 5, 2010 at 11:35 AM, Simon Riggs si...@2ndquadrant.com wrote: * It appears we would be in violation of the standard on 14.12 General Rule 6 a) i) 2) B), p.890 (Oh, I wish I was joking, there really is such a paragraph number) Just shoot me. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Needs Suggestion
I need suggestion about how region based memory management is done in postgres. I know the concept of region based memory management and also know about the functions like memorycontextswitch(). But I am not understanding how Postgres uses hierarchical, region-based memory management. That is I am not getting the inner idea or meaning of the code. Currently, my project topic is Parallelizing the spatial join using POSIX threads, so I have to understand the inner details and meanings of the code. I am using ddd to step through its code, from there I am getting the flow of the code but not understanding the semantics of its data-structures and its region based memory management. Kindly, if anybody can give some Ideas or Suggestions. Thank You, Subham Roy, CSE, IIT Bombay. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Sorry I thought Zoltan's explanation was clear enough. All prior ECPG versions were fine because dynamic cursor names were only added in 9.0. Apparently only this one place was missed. So this is a bug in the new feature, however not such a major one that it warrants the complete removal IMO. I'd prefer to fix this in 9.0.1. Hope this cleans it up a bit. Michael Kevin Grittner kevin.gritt...@wicourts.gov schrieb: Michael Meskes mich...@fam-meskes.de wrote: I'd consider this a bug. Could you explain why? The assertions that people consider it a bug without explanation of *why* is confusing for me. It sounds more like a feature of the ECPG interface that people would really like, and which has been technically possible since PostgreSQL 8.3, but for which nobody submitted a patch until this week. There was some hint that a 9.0 ECPG patch added new features which might make people expect this feature to have also been added. If this patch isn't necessarily correct, and would be dangerous to apply at this point, should the other patch be reverted as something which shouldn't go out without this feature? -Kevin -- Sent from my Android phone with K-9 Mail. Please excuse my brevity. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online backup cause boot failure, anyone know why?
2010/8/5 Richard husttrip...@vip.sina.com: All jods are done by client code, not manually. What is a jod? I still did't not understand what you said. What break what? The fact that you replaced CHECKPOINT_WAIT with CHECKPOINT_IMMEDIATE is the cause of your problem. You broke the correctness of the system by doing so. Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Concurrent MERGE
Looks like MERGE is progressing well. At 2010 Dev Mtg, we put me down to work on making merge work concurrently. That was garbled slightly and had me down as working on predicate locking which is the general solution to the problem. Do we still need me to work on concurrent MERGE, or is that included in the current MERGE patch (can't see it), or is that covered elsewhere (for example Kevin Grittner's recent work)? Still happy to do work as proposed, just checking still required. Thanks, -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MERGE Specification
On Thu, Aug 5, 2010 at 7:25 AM, Simon Riggs si...@2ndquadrant.com wrote: Also had these fragments as well, if they're still useful. Probably just useful as pointers as to what else to change to include the docs. The tests and docs were written from SQL standard, so any deviations would need to be flagged. The idea of writing the tests first was that they provide an objective test of whether the implementation works according to spec. I'd quite like a commentary on anything that needs changing. Not saying I will necessarily object to differences, but knowing the differences sounds important for us. I think this is a wonderful feature. A couple of thoughts: *) Would however very much like to see RETURNING support if it's not there. Our other DML statements support it, and this one should too. wCTE if/when we get it will make the lack of it especially glaring. (OTOH, no issue if there is no rule support...they should be deprecated) *) The decision to stay on the standard and not do a 'race free' version was IMO a good one. I am starting to come around to the point of view that the *only* safe way to guarantee race free merge with the current locking model is to take an appropriate table lock. BTW, our pl/pgsql upsert example we've been encouraging people to use has a horrible bug (see: http://postgresql.1045698.n5.nabble.com/Danger-of-idiomatic-plpgsql-loop-for-merging-data-td2257700.html). If we want to rework the locking model to support anticipatory locks then fine (but that has nothing to do with MERGE specifically). merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Re: [HACKERS] Online backup cause boot failure, anyone know why?
Sorry, wrong word, it should be job. You mean the wrong type of checkpoint causes XLOG file recovery fail? I was confused, the XLOG files seem corrupted, is it also caused by the checkpoint type? If so , why it can do this? -- Richard 2010-08-05 - 发件人：Nicolas Barbier 发送日期：2010-08-05 23:43:22 收件人：Richard 抄送：Heikki Linnakangas; Tom Lane; pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? 2010/8/5 Richard husttrip...@vip.sina.com: All jods are done by client code, not manually. What is a jod? I still did't not understand what you said. What break what? The fact that you replaced CHECKPOINT_WAIT with CHECKPOINT_IMMEDIATE is the cause of your problem. You broke the correctness of the system by doing so. Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Excerpts from Michael Meskes's message of jue ago 05 05:39:46 -0400 2010: Sorry I thought Zoltan's explanation was clear enough. All prior ECPG versions were fine because dynamic cursor names were only added in 9.0. Apparently only this one place was missed. So this is a bug in the new feature, however not such a major one that it warrants the complete removal IMO. I'd prefer to fix this in 9.0.1. Since we're still in the beta phase, it makes sense to apply the fix right now so that it appears in 9.0. No point in waiting for 9.0.1. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrent MERGE
On Thu, Aug 5, 2010 at 11:43 AM, Simon Riggs si...@2ndquadrant.com wrote: Looks like MERGE is progressing well. At 2010 Dev Mtg, we put me down to work on making merge work concurrently. That was garbled slightly and had me down as working on predicate locking which is the general solution to the problem. Do we still need me to work on concurrent MERGE, or is that included in the current MERGE patch (can't see it), or is that covered elsewhere (for example Kevin Grittner's recent work)? Still happy to do work as proposed, just checking still required. I suspect Kevin's patch will solve it if using a sufficiently high transaction isolation level, but something else might be needed otherwise. However, I confess to ignorance as to the underlying issues? Why is MERGE worse in this regard than, say, UPDATE? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Alvaro Herrera írta: Excerpts from Michael Meskes's message of jue ago 05 05:39:46 -0400 2010: Sorry I thought Zoltan's explanation was clear enough. All prior ECPG versions were fine because dynamic cursor names were only added in 9.0. Apparently only this one place was missed. So this is a bug in the new feature, however not such a major one that it warrants the complete removal IMO. I'd prefer to fix this in 9.0.1. Since we're still in the beta phase, it makes sense to apply the fix right now so that it appears in 9.0. No point in waiting for 9.0.1. It boils down to the fact that Michael doesn't have too much time and no one else knows ECPG in depth. So... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrent MERGE
On 05/08/10 18:43, Simon Riggs wrote: Do we still need me to work on concurrent MERGE, or is that included in the current MERGE patch (can't see it), or ... It's not in the current MERGE patch. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Needs Suggestion
sub...@cse.iitb.ac.in writes: I need suggestion about how region based memory management is done in postgres. Have you read src/backend/utils/mmgr/README ? It's old but still reasonably accurate. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrent MERGE
On 05/08/10 18:57, Robert Haas wrote: On Thu, Aug 5, 2010 at 11:43 AM, Simon Riggssi...@2ndquadrant.com wrote: Looks like MERGE is progressing well. At 2010 Dev Mtg, we put me down to work on making merge work concurrently. That was garbled slightly and had me down as working on predicate locking which is the general solution to the problem. Do we still need me to work on concurrent MERGE, or is that included in the current MERGE patch (can't see it), or is that covered elsewhere (for example Kevin Grittner's recent work)? Still happy to do work as proposed, just checking still required. I suspect Kevin's patch will solve it if using a sufficiently high transaction isolation level, but something else might be needed otherwise. With truly serializable isolation I think you'll get a serialization failure error. However, I confess to ignorance as to the underlying issues? Why is MERGE worse in this regard than, say, UPDATE? MERGE can be used to implement upsert, where a row is updated if it exists and inserted if it doesn't. I don't think Kevin's patch will suffice for that. You don't usually want a serialization failure error when you run two upserts at the same time, you want both of them to succeed, one doing an insert and the other one doing an update. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Boszormenyi Zoltan z...@cybertec.at writes: Alvaro Herrera Ãrta: Since we're still in the beta phase, it makes sense to apply the fix right now so that it appears in 9.0. No point in waiting for 9.0.1. It boils down to the fact that Michael doesn't have too much time and no one else knows ECPG in depth. So... Yeah. I think what Michael is saying is he doesn't have time to review the patch now and doesn't want to hold up 9.0 until he does. We can delay 9.0 for him, or apply the patch unreviewed, or allow 9.0 to go out with this as a known bug. I don't much care for #2, given the size of the patch. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Re: [HACKERS] Online backup cause boot failure,anyone know why?
Let's be clear. If you change the postgres code and then things break I think you're pretty much on your own. We can accept some responsibility for helping you if you're running our code, but not if you're running our code which you have subsequently mangled. If you break things you get to fix them. cheers andrew On 08/05/2010 10:20 AM, Richard wrote: Oh sorry, I missed something. I turned off the XLOG archive in code after pg_start_backup so the pg_xlog directory contains all the xlog files. And for performance purpose, I change the checkpoint type in pg_start_backup to CHECKPOINT_IMMEDIATE, does it matter? The PG log I mentioned above is the running error log not the XLOG. -- Richard 2010-08-05 - 发件人：Robert Haas 发送日期：2010-08-05 22:07:45 收件人：Richard 抄送：Andrew Dunstan; pgsql-hackers 主题：Re: [HACKERS] Re: Re: [HACKERS] Online backup cause boot failure,anyone know why? On Thu, Aug 5, 2010 at 9:50 AM, Richardhusttrip...@vip.sina.com wrote: I reboot PG because I found PG recovery end point if far away from the actual end point of the XLOG on the backup directory, so I want to test if the original DB is OK. Unfortunately, I got the same PG log on the original DB. I don't unstand what you said, I missing what? The transaction logs archived during the backup? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrent MERGE
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: However, I confess to ignorance as to the underlying issues? Why is MERGE worse in this regard than, say, UPDATE? MERGE can be used to implement upsert, where a row is updated if it exists and inserted if it doesn't. I don't think Kevin's patch will suffice for that. You don't usually want a serialization failure error when you run two upserts at the same time, you want both of them to succeed, one doing an insert and the other one doing an update. The patch Dan and I are working on won't block anything that snapshot isolation doesn't already block, so if the behavior you want is that one is held up until the other is done with something, it's not going to help. What it would do is detect whether two concurrent upserts are behaving in a way that is consistent with some serial execution of the two upserts; it would do nothing if there was a consistent interpretation, but roll one back if each appeared to come before the other in some respect. All of that, of course, with the usual caveats that it would have *no* impact unless both were run at the SERIALIZABLE isolation level, there could be false positives, and the MERGE code might possibly need to add a few calls to the functions added in the serializable patch. I hope that clarified rather than muddied the waters -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrent MERGE
robertmh...@gmail.com (Robert Haas) writes: On Thu, Aug 5, 2010 at 11:43 AM, Simon Riggs si...@2ndquadrant.com wrote: Looks like MERGE is progressing well. At 2010 Dev Mtg, we put me down to work on making merge work concurrently. That was garbled slightly and had me down as working on predicate locking which is the general solution to the problem. Do we still need me to work on concurrent MERGE, or is that included in the current MERGE patch (can't see it), or is that covered elsewhere (for example Kevin Grittner's recent work)? Still happy to do work as proposed, just checking still required. I suspect Kevin's patch will solve it if using a sufficiently high transaction isolation level, but something else might be needed otherwise. However, I confess to ignorance as to the underlying issues? Why is MERGE worse in this regard than, say, UPDATE? It's worse than UPDATE because - It could be an INSERT, if the data's new, but - If the data's there, it becomes an UPDATE, but - If some concurrent update has just DELETEd the data that's there, it becomes an INSERT again, but - Oops, that DELETE rolled bac, so it's an UPDATE again... Recurse as needed to make it more undecidable as to whether it's really an INSERT or an UPDATE :-). -- Rules of the Evil Overlord #208. Members of my Legion of Terror will attend seminars on Sensitivity Training. It's good public relations for them to be kind and courteous to the general population when not actively engaged in sowing chaos and destruction. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Two different methods of sneaking non-immutable data into an index
mmonc...@gmail.com (Merlin Moncure) writes: On Wed, Aug 4, 2010 at 9:31 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Aug 4, 2010 at 6:43 PM, Merlin Moncure mmonc...@gmail.com wrote: *) also, isn't it possible to change text cast influencing GUCs 'n' times per statement considering any query can call a function and any function can say, change datestyle? Shouldn't the related functions be marked 'volatile', not stable? This is just evil. It seems to me that we might want to instead prevent functions from changing things for their callers, or postponing any such changes until the end of the statement, or, uh, something. We can't afford to put ourselves in a situation of having to make everything volatile; at least, not if performance is anywhere in our top 50 goals. yeah -- perhaps you shouldn't be allowed set things like datestyle in functions then. I realize this is a corner (of the universe) case, but I can't recall any other case of volatility being relaxed on performance grounds... :-). Maybe a documentation warning would suffice? That would cause grief for Slony-I, methinks, and probably other things that behave somewhat similar. The logtrigger() function coerces datestyle to ISO, so that when dates get stored, they are stored in a canonical form, irrespective of an individual connection's decisions on datestyle, so we don't have to include datestyle information as part of the replicated data. -- output = reverse(moc.liamg @ enworbbc) http://linuxfinances.info/info/postgresql.html Chaotic Evil means never having to say you're sorry. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pgsql-hack...@news.hub.org 37% OFF on Pfizer!
http://groups.yahoo.com/group/syrilalwinl/message n Bayern 646 Ludwig III. von Bayern: Gesuch Hitlers an L. 179 Lueger, Dr. Karl, BegrunderderChristlich-sozialen Partei (s. diese): L. und die Christlich-soziale Partei 58. Burgermeister von Wien 74, 107, 108, 133 Madchenerziehung im volkischen Staat 454. Vgl. Erziehung Madchenhandel und Judentum 63 Marx, Karl, Begrunder des Marxismus 234, 420, 532. Staatslehre 434 Marxismus: Verkennen 184. Kern 351. Kulturzerstorer 69. Von der westlichen Demokratie gefordert 85. M. und Demokratie 412. M. und Judentum 350 f., 352, 498. Staatsauffassung 420. V -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Two different methods of sneaking non-immutable data into an index
On Thu, Aug 5, 2010 at 12:59 PM, Chris Browne cbbro...@acm.org wrote: mmonc...@gmail.com (Merlin Moncure) writes: On Wed, Aug 4, 2010 at 9:31 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Aug 4, 2010 at 6:43 PM, Merlin Moncure mmonc...@gmail.com wrote: *) also, isn't it possible to change text cast influencing GUCs 'n' times per statement considering any query can call a function and any function can say, change datestyle? Shouldn't the related functions be marked 'volatile', not stable? This is just evil. It seems to me that we might want to instead prevent functions from changing things for their callers, or postponing any such changes until the end of the statement, or, uh, something. We can't afford to put ourselves in a situation of having to make everything volatile; at least, not if performance is anywhere in our top 50 goals. yeah -- perhaps you shouldn't be allowed set things like datestyle in functions then. I realize this is a corner (of the universe) case, but I can't recall any other case of volatility being relaxed on performance grounds... :-). Maybe a documentation warning would suffice? That would cause grief for Slony-I, methinks, and probably other things that behave somewhat similar. The logtrigger() function coerces datestyle to ISO, so that when dates get stored, they are stored in a canonical form, irrespective of an individual connection's decisions on datestyle, so we don't have to include datestyle information as part of the replicated data. hm -- interesting -- couldn't that cause exactly the sort of situation though where stability of statement is violated? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Two different methods of sneaking non-immutable data into an index
Chris Browne cbbro...@acm.org writes: mmonc...@gmail.com (Merlin Moncure) writes: yeah -- perhaps you shouldn't be allowed set things like datestyle in functions then. That would cause grief for Slony-I, methinks, and probably other things that behave somewhat similar. Yeah, it's not really practical (or useful IMO) to try to lock this down completely. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrent MERGE
Chris Browne cbbro...@acm.org wrote: robertmh...@gmail.com (Robert Haas) writes: I suspect Kevin's patch will solve it if using a sufficiently high transaction isolation level, but something else might be needed otherwise. However, I confess to ignorance as to the underlying issues? Why is MERGE worse in this regard than, say, UPDATE? It's worse than UPDATE because - It could be an INSERT, if the data's new, but - If the data's there, it becomes an UPDATE, but - If some concurrent update has just DELETEd the data that's there, it becomes an INSERT again, but - Oops, that DELETE rolled bac, so it's an UPDATE again... Recurse as needed to make it more undecidable as to whether it's really an INSERT or an UPDATE :-). Not to get too far into the serializable issues, but the server won't do any such recursion with the serializable patch. Each serializable transaction would have its own snapshot where the row was there or it wasn't, and would act accordingly. If they took conflicting actions on the same row, one of them might be rolled back with a serialization failure. The client is likely to want to retry the operation based on the SQLSTATE indicating serialization failure, which (as the patch stands now) could result in some head-banging if the client doesn't introduce some delay first. I have an optimization in mind (described on the Wiki page) which could help with that, but its impact on overall performance is uncertain, so I don't want to mess with that until we have more benchmarks in place for realistic loads which might use serializable isolation. So... No, it's not directly a problem on the server itself. Yes, a client can make it a problem by resubmitting failed queries too quickly. But, we might be able to fix that with additional work. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrent MERGE
At 2010 Dev Mtg, we put me down to work on making merge work concurrently. That was garbled slightly and had me down as working on predicate locking which is the general solution to the problem. Well, we *still* want predicate locking regardless of what MERGE supports. It's useful in about 9 different ways. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrent MERGE
I wrote: So... No, it's not directly a problem on the server itself. I just had a thought -- the MERGE code isn't doing anything fancy with snapshots, is it? I haven't been tracking that discussion too closely or read the patch. My previous comments assume that the *snapshot* is stable for the duration of a MERGE command, at least if the transaction isolation level is serializable. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] remove upsert example from docs
Attached is a patch to remove the upsert example from the pl/pgsql documentation. It has a serious bug (see: http://www.spinics.net/lists/pgsql/msg112560.html) which is nontrivial to fix. IMNSHO, our code examples should encourage good practices and style. The 'correct' way to do race free upsert is to take a table lock first -- you don't have to loop or open a subtransaction. A high concurrency version is nice but is more of a special case solution (it looks like concurrent MERGE might render the issue moot anyways). merlin Index: doc/src/sgml/plpgsql.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.156 diff -c -6 -r1.156 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 29 Jul 2010 19:34:40 - 1.156 --- doc/src/sgml/plpgsql.sgml 5 Aug 2010 17:34:54 - *** *** 2332,2382 linkend=errcodes-table for a list of possible error codes). The varnameSQLERRM/varname variable contains the error message associated with the exception. These variables are undefined outside exception handlers. /para - example id=plpgsql-upsert-example - titleExceptions with commandUPDATE//commandINSERT//title - para - - This example uses exception handling to perform either - commandUPDATE/ or commandINSERT/, as appropriate: - - programlisting - CREATE TABLE db (a INT PRIMARY KEY, b TEXT); - - CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS - $$ - BEGIN - LOOP - -- first try to update the key - UPDATE db SET b = data WHERE a = key; - IF found THEN - RETURN; - END IF; - -- not there, so try to insert the key - -- if someone else inserts the same key concurrently, - -- we could get a unique-key failure - BEGIN - INSERT INTO db(a,b) VALUES (key, data); - RETURN; - EXCEPTION WHEN unique_violation THEN - -- do nothing, and loop to try the UPDATE again - END; - END LOOP; - END; - $$ - LANGUAGE plpgsql; - - SELECT merge_db(1, 'david'); - SELECT merge_db(1, 'dennis'); - /programlisting - - /para - /example /sect2 /sect1 sect1 id=plpgsql-cursors titleCursors/title --- 2332,2343 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Performance Enhancement/Fix for Array Utility Functions
Robert Haas robertmh...@gmail.com wrote: On Wed, Jul 28, 2010 at 1:20 AM, Mike Lewis mikelikes...@gmail.com wrote: 1. As-is, it's a significant *pessimization* for small arrays, because the heap_tuple_untoast_attr_slice code does a palloc/copy even when one is not needed because the data is already not toasted. I think there needs to be a code path that avoids that. This seems like it shouldn't be too hard to fix, and I think it should be fixed. Do you have any suggestions where to start? I do agree that this should be fixed as well. I don't have too much time to dedicate to this project. I can try to put in some time this weekend though if it isn't looking too bad. Perhaps you could check VARATT_IS_EXTENDED. If that's true, then slice it, but if it's false, then just use the original datum. You might want to wrap that up in a function rather than cramming it all in the macro definition, though. As Mike hasn't been able to find the time to get to this yet, I'm marking this as Returned with Feedback. Hopefully the issues can be addressed in the next five weeks and we can pick it up again in the next CommitFest. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stat_user_functions' notion of user
On 8/5/10 6:58 AM, Peter Eisentraut wrote: pg_stat_user_functions has an inconsistent notion of what user is. Whereas the other pg_stat_user_* views filter out non-user objects by schema, pg_stat_user_functions checks for language internal, which does not successfully exclude builtin functions of language SQL. Is there a reason for this inconsistency? Undoubtedly because function data collection already filters on function language, per the GUC setting. Not that that is a *good* reason, but I can see how we arrived a the current functionality. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Two different methods of sneaking non-immutable data into an index
On Thu, Aug 5, 2010 at 12:59 PM, Chris Browne cbbro...@acm.org wrote: mmonc...@gmail.com (Merlin Moncure) writes: On Wed, Aug 4, 2010 at 9:31 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Aug 4, 2010 at 6:43 PM, Merlin Moncure mmonc...@gmail.com wrote: *) also, isn't it possible to change text cast influencing GUCs 'n' times per statement considering any query can call a function and any function can say, change datestyle? Shouldn't the related functions be marked 'volatile', not stable? This is just evil. It seems to me that we might want to instead prevent functions from changing things for their callers, or postponing any such changes until the end of the statement, or, uh, something. We can't afford to put ourselves in a situation of having to make everything volatile; at least, not if performance is anywhere in our top 50 goals. yeah -- perhaps you shouldn't be allowed set things like datestyle in functions then. I realize this is a corner (of the universe) case, but I can't recall any other case of volatility being relaxed on performance grounds... :-). Maybe a documentation warning would suffice? That would cause grief for Slony-I, methinks, and probably other things that behave somewhat similar. The logtrigger() function coerces datestyle to ISO, so that when dates get stored, they are stored in a canonical form, irrespective of an individual connection's decisions on datestyle, so we don't have to include datestyle information as part of the replicated data. I think functions should be allowed to change GUCs internally, but maybe not for the context from which they were called. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove upsert example from docs
Merlin Moncure mmonc...@gmail.com wrote: Attached is a patch to remove the upsert example from the pl/pgsql documentation. It has a serious bug (see: http://www.spinics.net/lists/pgsql/msg112560.html) which is nontrivial to fix. IMNSHO, our code examples should encourage good practices and style. The 'correct' way to do race free upsert is to take a table lock first -- you don't have to loop or open a subtransaction. A high concurrency version is nice but is more of a special case solution (it looks like concurrent MERGE might render the issue moot anyways). Of course, this can be done safely without a table lock if either or both of the concurrency patches (one by Florian, one by Dan and myself) get committed, so maybe we should wait to see whether either of them makes it before adjusting the docs on this point -- at least for 9.1. Taking a broken example out of 9.0 and back branches might make sense -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Performance Enhancement/Fix for Array Utility Functions
I started taking a look at the internals of the detoast functions and I came to the conclusion that I didn't have sufficient understanding of what was going on to make the correct changes, nor sufficient time to gain that understanding. Sorry for not getting back sooner. There are a lot of different cases for the detoast stuff, and I think I would need a full understanding of toast functionality. (for example, I didn't even know there was lzma compression in postgres until one of the replies to this thread) Thanks, Mike -- Michael Lewis lolrus.org mikelikes...@gmail.com On Thu, Aug 5, 2010 at 10:52 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: On Wed, Jul 28, 2010 at 1:20 AM, Mike Lewis mikelikes...@gmail.com wrote: 1. As-is, it's a significant *pessimization* for small arrays, because the heap_tuple_untoast_attr_slice code does a palloc/copy even when one is not needed because the data is already not toasted. I think there needs to be a code path that avoids that. This seems like it shouldn't be too hard to fix, and I think it should be fixed. Do you have any suggestions where to start? I do agree that this should be fixed as well. I don't have too much time to dedicate to this project. I can try to put in some time this weekend though if it isn't looking too bad. Perhaps you could check VARATT_IS_EXTENDED. If that's true, then slice it, but if it's false, then just use the original datum. You might want to wrap that up in a function rather than cramming it all in the macro definition, though. As Mike hasn't been able to find the time to get to this yet, I'm marking this as Returned with Feedback. Hopefully the issues can be addressed in the next five weeks and we can pick it up again in the next CommitFest. -Kevin
Re: [HACKERS] remove upsert example from docs
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Attached is a patch to remove the upsert example from the pl/pgsql documentation. It has a serious bug (see: http://www.spinics.net/lists/pgsql/msg112560.html) which is nontrivial to fix. IMNSHO, our code examples should encourage good practices and style. No, removing is a bad idea, as it's referenced from here to the North Pole and back. Better would simply be a warning about the non uniqueness of the unique constraint message. The 'correct' way to do race free upsert is to take a table lock first -- you don't have to loop or open a subtransaction. A high concurrency version is nice but is more of a special case solution (it looks like concurrent MERGE might render the issue moot anyways). I think anything doing table locks should be the special case solution as production systems generally avoid full table locks like the plague. The existing solution works fine as long as we explain that caveat (which is a little bit of a corner case, else we'd have heard more complaints before now). - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201008051402 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkxa/XgACgkQvJuQZxSWSsjTbACfcjrsBVXCOGUb6foARfNIztSo AswAn0bNttP8XOs/2tw6jFsSa0cZkq7e =HUcq -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove upsert example from docs
Merlin Moncure mmonc...@gmail.com writes: Attached is a patch to remove the upsert example from the pl/pgsql documentation. It has a serious bug (see: http://www.spinics.net/lists/pgsql/msg112560.html) which is nontrivial to fix. IMNSHO, our code examples should encourage good practices and style. I was not persuaded that there's a real bug in practice. IMO, his problem was a broken trigger not broken upsert logic. Even if we conclude this is unsafe, simply removing the example is of no help to anyone. A more useful response would be to supply a correct example. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Two different methods of sneaking non-immutable data into an index
mmonc...@gmail.com (Merlin Moncure) writes: On Thu, Aug 5, 2010 at 12:59 PM, Chris Browne cbbro...@acm.org wrote: mmonc...@gmail.com (Merlin Moncure) writes: On Wed, Aug 4, 2010 at 9:31 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Aug 4, 2010 at 6:43 PM, Merlin Moncure mmonc...@gmail.com wrote: *) also, isn't it possible to change text cast influencing GUCs 'n' times per statement considering any query can call a function and any function can say, change datestyle? Shouldn't the related functions be marked 'volatile', not stable? This is just evil. It seems to me that we might want to instead prevent functions from changing things for their callers, or postponing any such changes until the end of the statement, or, uh, something. We can't afford to put ourselves in a situation of having to make everything volatile; at least, not if performance is anywhere in our top 50 goals. yeah -- perhaps you shouldn't be allowed set things like datestyle in functions then. I realize this is a corner (of the universe) case, but I can't recall any other case of volatility being relaxed on performance grounds... :-). Maybe a documentation warning would suffice? That would cause grief for Slony-I, methinks, and probably other things that behave somewhat similar. The logtrigger() function coerces datestyle to ISO, so that when dates get stored, they are stored in a canonical form, irrespective of an individual connection's decisions on datestyle, so we don't have to include datestyle information as part of the replicated data. hm -- interesting -- couldn't that cause exactly the sort of situation though where stability of statement is violated? It shouldn't... The data gets stored physically, on disk, in a canonical form. Why should it be unstable to capture data in a canonical form, when that's what gets stored on disk? -- (format nil ~...@~s cbbrowne gmail.com) The statistics on sanity are that one out of every four Americans is suffering from some form of mental illness. Think of your three best friends. If they're okay, then it's you. -- Rita Mae Brown -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove upsert example from docs
On 08/05/2010 02:09 PM, Tom Lane wrote: Merlin Moncuremmonc...@gmail.com writes: Attached is a patch to remove the upsert example from the pl/pgsql documentation. It has a serious bug (see: http://www.spinics.net/lists/pgsql/msg112560.html) which is nontrivial to fix. IMNSHO, our code examples should encourage good practices and style. I was not persuaded that there's a real bug in practice. IMO, his problem was a broken trigger not broken upsert logic. Even if we conclude this is unsafe, simply removing the example is of no help to anyone. A more useful response would be to supply a correct example. Yeah, that's how it struck me just now. Maybe we should document that the inserts had better not fire a trigger that could cause an uncaught uniqueness violation exception. You could also possibly usefully prevent infinite looping in such cases by using a limited loop rather an unlimited loop. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
I wrote: Well, I forgot that an aggregate involves more than one catalog row ;-). So it's a bit bigger patch than that, but still pretty small and safe. See attached. Applied to HEAD and 9.0. The mistaken case will now yield this: regression=# select string_agg(f1 order by f1, ',') from text_tbl; ERROR: function string_agg(text) does not exist LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. It's not perfect (I don't think it's practical to get the HINT to read Put the ORDER BY at the end ;-)) but at least it should get people pointed in the right direction when they do this. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On ons, 2010-08-04 at 18:19 -0400, Tom Lane wrote: This policy also implies that we are never going to allow default arguments for aggregates, or at least never have any built-in ones that use such a feature. By my count the following people had offered an opinion on making this change: for: tgl, josh, badalex, mmoncure against: rhaas, thom Anybody else want to vote, or change their vote after seeing the patch? I vote against this patch. There are plenty of other places that SQL is confusing, and this move seems excessive to me, and I find the functionality that is proposed for removal quite useful. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Peter Eisentraut pete...@gmx.net writes: I vote against this patch. There are plenty of other places that SQL is confusing, and this move seems excessive to me, and I find the functionality that is proposed for removal quite useful. Huh? The functionality proposed for removal is only that of omitting an explicit delimiter argument for string_agg(). Since the default value (an empty string) doesn't seem to be the right thing all that often anyway, I'm not following why you think this is a significant downgrade. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Aug 5, 2010, at 11:25 AM, Tom Lane wrote: Applied to HEAD and 9.0. The mistaken case will now yield this: regression=# select string_agg(f1 order by f1, ',') from text_tbl; ERROR: function string_agg(text) does not exist LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; ^ I'm confused: that looks like the two-argument form to me. Have I missed something? HINT: No function matches the given name and argument types. You might need to add explicit type casts. It's not perfect (I don't think it's practical to get the HINT to read Put the ORDER BY at the end ;-)) but at least it should get people pointed in the right direction when they do this. It confuses the shit out of me. It says string_agg(text) doesn't exist when that clearly is not the name of the function you've called. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CommitFest 2010-07 week three progress report
Kevin Grittner kevin.gritt...@wicourts.gov wrote: New numbers on where we are with this CommitFest, at the end of the third week: 72 patches were submitted 3 patches were withdrawn (deleted) by their authors 12 patches were moved to CommitFest 2010-09 -- 57 patches in CommitFest 2010-07 -- 3 committed to 9.0 -- 54 patches for 9.1 -- 1 rejected 17 returned with feedback 21 committed for 9.1 -- 39 disposed -- 15 pending 9 ready for committer -- 6 will still need reviewer attention 1 waiting on author to respond to review -- 5 patches need review now and have a reviewer assigned Of the four patches moved to the next CF, one was because we couldn't find a reviewer for ECPG code at this time, one was because both Florian and I would like to work up some additional tests for the serializable lock consistency patch before sending it to a committer, and two were because Itagaki changed jobs and didn't have time during this CF to finish reviews already well underway. With only ten days to go, in order to leave time for committers to do their thing, we need to be wrapping up the remaining patches. I think we look pretty good. Of the remaining six patches, two are Work in Progress, so are not expected to go to a committer; three involve a committer, so I figure they can decide when and if it's time to return or move them, which just leaves one which is down to tweaking docs. The WIP patch for serializable transactions with predicate locking patch has yet to have a review posted, although there have been off-list discussions. The reviewer had to put it aside for about a week due to job pressures, but is reported back on it. (The suspense is killing me.) Last week: 72 patches were submitted 3 patches were withdrawn (deleted) by their authors 8 patches were moved to CommitFest 2010-09 -- 61 patches in CommitFest 2010-07 -- 3 committed to 9.0 -- 58 patches for 9.1 -- 1 rejected 13 returned with feedback 12 committed for 9.1 -- 26 disposed -- 32 pending 10 ready for committer -- 22 will still need reviewer attention 7 waiting on author to respond to review -- 15 need review before further action 2 Needs Review patches don't have a reviewer assigned -- 13 patches need review and have a reviewer assigned -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On 5 August 2010 19:39, David E. Wheeler da...@kineticode.com wrote: On Aug 5, 2010, at 11:25 AM, Tom Lane wrote: Applied to HEAD and 9.0. The mistaken case will now yield this: regression=# select string_agg(f1 order by f1, ',') from text_tbl; ERROR: function string_agg(text) does not exist LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; ^ I'm confused: that looks like the two-argument form to me. Have I missed something? HINT: No function matches the given name and argument types. You might need to add explicit type casts. It's not perfect (I don't think it's practical to get the HINT to read Put the ORDER BY at the end ;-)) but at least it should get people pointed in the right direction when they do this. It confuses the shit out of me. It says string_agg(text) doesn't exist when that clearly is not the name of the function you've called. What function name do you believe was called? -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Thu, Aug 5, 2010 at 12:25, Tom Lane t...@sss.pgh.pa.us wrote: regression=# select string_agg(f1 order by f1, ',') from text_tbl; ERROR: function string_agg(text) does not exist LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. It's not perfect (I don't think it's practical to get the HINT to read Put the ORDER BY at the end ;-)) but at least it should get people pointed in the right direction when they do this. Not to mention I think most of the confusion came from using the 1 argument version first (with an order by) and then jumping straight to the 2 arg version. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove upsert example from docs
On Thu, Aug 5, 2010 at 2:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: Attached is a patch to remove the upsert example from the pl/pgsql documentation. It has a serious bug (see: http://www.spinics.net/lists/pgsql/msg112560.html) which is nontrivial to fix. IMNSHO, our code examples should encourage good practices and style. I was not persuaded that there's a real bug in practice. IMO, his problem was a broken trigger not broken upsert logic. Even if we conclude this is unsafe, simply removing the example is of no help to anyone. Well, the error handler is assuming that the unique_volation is coming from the insert made within the loop. This is obviously not a safe assumption in an infinite loop context. It should be double checking where the error was being thrown from -- but the only way I can think of to do that is to check sqlerrm. Or you arguing that if you're doing this, all dependent triggers must not throw unique violations up the exception chain? Looping N times and punting is meh: since you have to now check in the app, why have this mechanism at all? A more useful response would be to supply a correct example. Agree: I'd go further I would argue to supply both the 'safe' and 'high concurrency (with caveat)' way. I'm not saying the example is necessarily bad, just that it's maybe not a good thing to be pointing as a learning example without qualifications. Then you get a lesson both on upsert methods and defensive error handling (barring objection, I'll provide that). merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
David E. Wheeler da...@kineticode.com writes: On Aug 5, 2010, at 11:25 AM, Tom Lane wrote: Applied to HEAD and 9.0. The mistaken case will now yield this: regression=# select string_agg(f1 order by f1, ',') from text_tbl; ERROR: function string_agg(text) does not exist I'm confused: that looks like the two-argument form to me. Have I missed something? Yeah, the whole point of the thread: that's not a call of a two-argument aggregate. It's a call of a one-argument aggregate, using a two-column sort key to order the aggregate input rows. It confuses the shit out of me. It says string_agg(text) doesn't exist when that clearly is not the name of the function you've called. Well, maybe we need to expend some more sweat on the error message then. But this patch was still a prerequisite thing, because without it there is no error that we can complain about. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Aug 5, 2010, at 11:42 AM, Thom Brown wrote: LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; ^ I'm confused: that looks like the two-argument form to me. Have I missed something? HINT: No function matches the given name and argument types. You might need to add explicit type casts. It's not perfect (I don't think it's practical to get the HINT to read Put the ORDER BY at the end ;-)) but at least it should get people pointed in the right direction when they do this. It confuses the shit out of me. It says string_agg(text) doesn't exist when that clearly is not the name of the function you've called. What function name do you believe was called? The message says: string_agg(f1 order by f1, ',') That looks like string_agg(text, text) or string_agg(anyelement, text). Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Aug 5, 2010, at 11:45 AM, Tom Lane wrote: I'm confused: that looks like the two-argument form to me. Have I missed something? Yeah, the whole point of the thread: that's not a call of a two-argument aggregate. It's a call of a one-argument aggregate, using a two-column sort key to order the aggregate input rows. OH!. Wow, weird. I never noticed that. It confuses the shit out of me. It says string_agg(text) doesn't exist when that clearly is not the name of the function you've called. Well, maybe we need to expend some more sweat on the error message then. But this patch was still a prerequisite thing, because without it there is no error that we can complain about. Yeah, understood. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove upsert example from docs
Merlin Moncure mmonc...@gmail.com writes: On Thu, Aug 5, 2010 at 2:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: I was not persuaded that there's a real bug in practice. IMO, his problem was a broken trigger not broken upsert logic. Even if we conclude this is unsafe, simply removing the example is of no help to anyone. Well, the error handler is assuming that the unique_volation is coming from the insert made within the loop. This is obviously not a safe assumption in an infinite loop context. Well, that's a fair point. Perhaps we should just add a note that if there are any triggers that do additional inserts/updates, the exception catcher had better check which table the unique_violation is being reported for. A more useful response would be to supply a correct example. Agree: I'd go further I would argue to supply both the 'safe' and 'high concurrency (with caveat)' way. I'm not saying the example is necessarily bad, just that it's maybe not a good thing to be pointing as a learning example without qualifications. Then you get a lesson both on upsert methods and defensive error handling (barring objection, I'll provide that). Have at it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Well, maybe we need to expend some more sweat on the error message then. But this patch was still a prerequisite thing, because without it there is no error that we can complain about. Yes, I'd say an addition to the HINT is in order *assuming* at that stage we can tell if the user passed an ORDER BY or not. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Josh Berkus j...@agliodbs.com writes: Well, maybe we need to expend some more sweat on the error message then. But this patch was still a prerequisite thing, because without it there is no error that we can complain about. Yes, I'd say an addition to the HINT is in order *assuming* at that stage we can tell if the user passed an ORDER BY or not. I was just looking at this, and realized I was mistaken earlier: the error is issued in ParseFuncOrColumn, which already is passed the agg_order list, so actually it's completely trivial to tell whether a variant error message is appropriate. I suggest that we key it off there being not just an ORDER BY, but an ORDER BY with more than one element; if there's only one then this cannot be the source of confusion. Next question: exactly how should the variant HINT be phrased? I'm inclined to drop the bit about explicit casts and make it read something like HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Thu, Aug 5, 2010 at 3:16 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: Well, maybe we need to expend some more sweat on the error message then. But this patch was still a prerequisite thing, because without it there is no error that we can complain about. Yes, I'd say an addition to the HINT is in order *assuming* at that stage we can tell if the user passed an ORDER BY or not. I was just looking at this, and realized I was mistaken earlier: the error is issued in ParseFuncOrColumn, which already is passed the agg_order list, so actually it's completely trivial to tell whether a variant error message is appropriate. I suggest that we key it off there being not just an ORDER BY, but an ORDER BY with more than one element; if there's only one then this cannot be the source of confusion. Next question: exactly how should the variant HINT be phrased? I'm inclined to drop the bit about explicit casts and make it read something like HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate. Could we arrange to emit this error message only when there is an aggregate with the same name but different arguments? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Aug 5, 2010, at 12:16 PM, Tom Lane wrote: HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate. +1 David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Robert Haas robertmh...@gmail.com writes: On Thu, Aug 5, 2010 at 3:16 PM, Tom Lane t...@sss.pgh.pa.us wrote: Next question: exactly how should the variant HINT be phrased? I'm inclined to drop the bit about explicit casts and make it read something like HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate. Could we arrange to emit this error message only when there is an aggregate with the same name but different arguments? That'd move it into the category of needing significant restructuring, I'm afraid. At the moment it's not apparent that it's worth it. We're already able to limit the use of the variant hint to a pretty darn narrow set of cases, and it is only a hint after all. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On 8/5/10 12:18 PM, Robert Haas wrote: Could we arrange to emit this error message only when there is an aggregate with the same name but different arguments? Personally, I don't see this as really necessary. Just mentioning ORDER BY in the hint will be enough to give people the right place to look. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrent MERGE
Josh Berkus j...@agliodbs.com wrote: Well, we *still* want predicate locking regardless of what MERGE supports. It's useful in about 9 different ways. I don't know whether this is the right time to discuss those 9 different uses, but just so everyone knows, the SIRead locks needed for the SSI implementation in the current serializable patch have some characteristics which may be exactly what you want (if you want cache invalidation or some such) or may render them totally useless from some purposes. (1) They don't block anything. Ever. Conflicts with writes are detected, and right now that is used to mark rw-conflicts between serializable transactions. I assume we may want to add listeners who can be signaled on such conflicts, too; but that isn't there now. (2) They are only acquired by serializable transactions. (3) They can survive the transaction which acquired them, and even the termination of the process which ran the transaction. Right now they go away when the last serializable transaction which overlapped the acquiring serializable transaction completes. If we add listeners, I assume we'd want to keep them as long as a listener was registered, probably with some timeout feature. Just so everyone knows what is and isn't there right now. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrent MERGE
On 8/5/10 12:33 PM, Kevin Grittner wrote: I don't know whether this is the right time to discuss those 9 different uses, but just so everyone knows, the SIRead locks needed for the SSI implementation in the current serializable patch have some characteristics which may be exactly what you want (if you want cache invalidation or some such) or may render them totally useless from some purposes. Yeah, I haven't wrapped my head around your stuff enough yet. I would say that having such locks available only for serializable transactions limits some of the uses I'm thinking of. Anyway, here's some of the uses I'm thinking of: (1) Pre-insert lock: you know that you're going to insert a record with PK=X later in a long-running SP, so you want to lock out other inserts of PK=X at the beginning of the procedure. (2) FK Locking: you plan to modify or delete a parent FK record in this transaction, so you want to prevent any updates or inserts on its related child records. (in my experience, FK-releated sharelocks are the #1 cause of deadlocking). (3) No-duplicate queueing: you want to create a queue table which doesn't accept duplicate events, but you don't want it to be a source of deadlocks. This is a variant of (1), but a common case. (4) Blackouts: records of type x aren't supposed to be created during period y to y1 or while procedure z is running. Predicate locking can be used to prevent this more easily than adding and removing a trigger. (5) Debugging: (variant of 4) records of type x keep getting inserted in the table, and you don't know where they're coming from. You can predicate lock to force an error and debug it. ... that's off the top of my head. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Josh Berkus j...@agliodbs.com writes: On 8/5/10 12:18 PM, Robert Haas wrote: Could we arrange to emit this error message only when there is an aggregate with the same name but different arguments? Personally, I don't see this as really necessary. Just mentioning ORDER BY in the hint will be enough to give people the right place to look. I suppose Robert is more concerned about the possibility that we emit the ORDER BY hint when that isn't really the source of the problem. But after all, the reason it's a hint and not the primary error message is that it's not certain to be helpful. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrent MERGE
Josh Berkus j...@agliodbs.com wrote: Anyway, here's some of the uses I'm thinking of: (1) Pre-insert lock: you know that you're going to insert a record with PK=X later in a long-running SP, so you want to lock out other inserts of PK=X at the beginning of the procedure. Well, if we added a listener, you could SELECT the desired key, and be notified of a conflicting insert, but that's not really what you're looking for. It does seem to me that you could solve this one by inserting the tuple and then updating it at the end, but I suppose you're looking to avoid the resulting dead tuple. Perhaps a listener could be fed to a cancel the conflicting query routine? In any event, the only resolution to such a conflict is to kill something, right? And right now, a write/write conflict would occur which would resolve it, you just want to be able to reserve the slot up front, so your transaction isn't canceled after doing a bunch of work, right? (2) FK Locking: you plan to modify or delete a parent FK record in this transaction, so you want to prevent any updates or inserts on its related child records. (in my experience, FK-releated sharelocks are the #1 cause of deadlocking). I don't see how that can be resolved without killing something, do you? You would just have to replace the current deadlock with some other form of serialization failure. (And no, I will never give up the position that a deadlock *is* one of many forms of serialization failure.) (3) No-duplicate queueing: you want to create a queue table which doesn't accept duplicate events, but you don't want it to be a source of deadlocks. This is a variant of (1), but a common case. I must be missing something. Please explain how this would work *without* serialization failures. As far as I can see, you can replace deadlocks with some other form, but I don't see the point. Basically, I think we should change the deadlock SQLSTATE to '40001' and any code which needs to deal with such things treats that SQLSTATE as meaning that wasn't a good time to try that transaction, try again in a bit. Or, if you just want it to do nothing if the row already exists, perhaps the new MERGE code would work? (4) Blackouts: records of type x aren't supposed to be created during period y to y1 or while procedure z is running. Predicate locking can be used to prevent this more easily than adding and removing a trigger. I would have thought that advisory locks covered this. In what way do they fall short for this use case? (5) Debugging: (variant of 4) records of type x keep getting inserted in the table, and you don't know where they're coming from. You can predicate lock to force an error and debug it. H Assuming fine enough granularity (like from an index for which a range could be locked to detect the conflict) adding a listener to the SIRead lock handling would be good for this. Well, as long as the transactions were serializable. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On tor, 2010-08-05 at 14:38 -0400, Tom Lane wrote: Huh? The functionality proposed for removal is only that of omitting an explicit delimiter argument for string_agg(). Since the default value (an empty string) doesn't seem to be the right thing all that often anyway, I'm not following why you think this is a significant downgrade. I just think it's useful to have the one-argument version. I understand the functionality is available in other ways. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrent MERGE
Kevin, Overall, you're missing the point: there are workarounds for all of these things now. However, they are *workarounds*, which means that they are awkward, expensive, and/or hard to administrate; having predicate locks would make things much easier. I don't see how that can be resolved without killing something, do you? You would just have to replace the current deadlock with some other form of serialization failure. (And no, I will never give up the position that a deadlock *is* one of many forms of serialization failure.) If you're in lock nowait mode, you could get back a can't lock error message immediately rather than waiting for the procedure to time out. There's certainly going to be an error regardless; it's a question of how expensive it is for the application and the database server. Deadlocks are *very* expensive, especially since our deadlock detector doesn't always figure them out successfully (which means the deadlock has to be resolved by the DBA). So any other type of serialization failure or error is better than deadlocking. I must be missing something. Please explain how this would work *without* serialization failures. As far as I can see, you can replace deadlocks with some other form, but I don't see the point. See above. (4) Blackouts: records of type x aren't supposed to be created during period y to y1 or while procedure z is running. Predicate locking can be used to prevent this more easily than adding and removing a trigger. I would have thought that advisory locks covered this. In what way do they fall short for this use case? Currently, I do use advisory locks for this case. However, they require a fair amount of administrative design and monitoring overhead. H Assuming fine enough granularity (like from an index for which a range could be locked to detect the conflict) adding a listener to the SIRead lock handling would be good for this. Well, as long as the transactions were serializable. Yeah, it's that last caveat which makes SIRead locks not as flexible as the theoretical predicate lock. Of course, any eventual actual implemenation of predicate locks might be equally inflexible. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PL/pgSQL EXECUTE '..' USING with unknown
There's a little problem with EXECUTE USING when the parameters are of
type unknown (going back to 8.4 where EXECUTE USING was introduced):
do $$
BEGIN
EXECUTE 'SELECT to_date($1, $2)' USING '17-DEC-80', 'DD-MON-YY';
END;
$$;
ERROR: failed to find conversion function from unknown to text
CONTEXT: SQL statement SELECT to_date($1, $2)
PL/pgSQL function inline_code_block line 2 at EXECUTE statement
The corresponding case works fine when used with PREPARE/EXECUTE:
postgres=# PREPARE foostmt AS SELECT to_date($1, $2);
PREPARE
postgres=# EXECUTE foostmt ('17-DEC-80', 'DD-MON-YY');
to_date
1980-12-17
(1 row)
With PREPARE/EXECUTE, the query is analyzed with
parse_analyze_varparams() which allows unknown param types to be deduced
from the context. Seems we should use that for EXECUTE USING as well,
but there's no SPI interface for that.
Thoughts? Should we add an SPI_prepare_varparams() function and use that?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
