Re: [HACKERS] Proof of concept: auto updatable views
On 27 August 2012 20:26, Dean Rasheed dean.a.rash...@gmail.com wrote: Here's an updated WIP patch which I'll add to the next commitfest. Re-sending gzipped (apparently the mail system corrupted it last time). Regards, Dean auto-update-views.patch.gz Description: GNU Zip compressed 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] Timing overhead and Linux clock sources
On Tue, Aug 28, 2012 at 6:13 AM, Greg Smith g...@2ndquadrant.com wrote: After staring at all the examples I generated again, I think Bruce is right that the newer format he's suggesting is better. I know I never thought about whether reordering for easier interpretation made sense before, and I'd also guess it was less coding for the existing order was the only reason Ants did it that way. That is correct, I didn't really have a preference for the order so I went for simpler is better. I agree that the order suggested by Bruce looks better. Having seen more measurements I'd have to say that the histogram is mostly useless. It seems to me that it mostly shows OS scheduling noise. I would even say that the histogram output should be hidden behind an command line option to avoid unnecessary confusion. Ants Aasma -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- 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] [v9.3] writable foreign tables
2012/8/27 Albe Laurenz laurenz.a...@wien.gv.at: Kohei KaiGai wrote: 2012/8/25 Robert Haas robertmh...@gmail.com: On Thu, Aug 23, 2012 at 1:10 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: It is a responsibility of FDW extension (and DBA) to ensure each foreign-row has a unique identifier that has 48-bits width integer data type in maximum. It strikes me as incredibly short-sighted to decide that the row identifier has to have the same format as what our existing heap AM happens to have. I think we need to allow the row identifier to be of any data type, and even compound. For example, the foreign side might have no equivalent of CTID, and thus use primary key. And the primary key might consist of an integer and a string, or some such. I assume it is a task of FDW extension to translate between the pseudo ctid and the primary key in remote side. For example, if primary key of the remote table is Text data type, an idea is to use a hash table to track the text-formed primary being associated with a particular 48-bits integer. The pseudo ctid shall be utilized to track the tuple to be modified on the scan-stage, then FDW can reference the hash table to pull-out the primary key to be provided on the prepared statement. And what if there is a hash collision? Then you would not be able to determine which row is meant. Even if we had a hash collision, each hash entry can have the original key itself to be compared. But anyway, I love the idea to support an opaque pointer to track particular remote-row rather. I agree with Robert that this should be flexible enough to cater for all kinds of row identifiers. Oracle, for example, uses ten byte identifiers which would give me a headache with your suggested design. Do we have some other reasonable ideas? Would it be too invasive to introduce a new pointer in TupleTableSlot that is NULL for anything but virtual tuples from foreign tables? I'm not certain whether the duration of TupleTableSlot is enough to carry a private datum between scan and modify stage. For example, the TupleTableSlot shall be cleared at ExecNestLoop prior to the slot being delivered to ExecModifyTuple. postgres=# EXPLAIN UPDATE t1 SET b = 'abcd' WHERE a IN (SELECT x FROM t2 WHERE x % 2 = 0); QUERY PLAN --- Update on t1 (cost=0.00..54.13 rows=6 width=16) - Nested Loop (cost=0.00..54.13 rows=6 width=16) - Seq Scan on t2 (cost=0.00..28.45 rows=6 width=10) Filter: ((x % 2) = 0) - Index Scan using t1_pkey on t1 (cost=0.00..4.27 rows=1 width=10) Index Cond: (a = t2.x) (6 rows) Is it possible to utilize ctid field to move a private pointer? TID data type is internally represented as a pointer to ItemPointerData, so it has enough width to track an opaque formed remote-row identifier; including string, int64 or others. One disadvantage is ctid system column shows a nonsense value when user explicitly references this system column. But it does not seems to me a fundamental problem, because we didn't give any special meaning on the ctid field of foreign table. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] [v9.3] writable foreign tables
2012/8/27 Shigeru HANADA shigeru.han...@gmail.com: Kaigai-san, On Thu, Aug 23, 2012 at 2:10 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: The patched portion at contrib/file_fdw.c does not make sense actually. It just prints messages for each invocation. It is just a proof-of-concept to show possibility of implementation based on real RDBMS. Attached is a tar ball of pgsql_fdw. It's WIP and contains no document, but it would be enough for your PoC purpose. Usage and features are same as the last version posted for 9.2 cycle. # I'll post finished patch in the CF-Sep. Thanks, it is helpful to work on. Here are random comments for your PoC patch: + As Robert says, using CTID as virtual tuple identifier doesn't seem nice when considering various FDWs for NoSQL or RDBMS. Having abstract layer between FDWs and tuple sounds better, but implementing it by each FDW seems useless effort. Do yo have any idea of generic mechanism for tuple mapping? As I wrote in the previous message, isn't it a reasonable idea to move a private datum (instead of alternate key) on the ctid field which has been internally represented as a pointer to indicate ItemPointerData? + Do you have any plan about deparsing local qualifiers into remote query to avoid repeated query submission? This would improve performance of big UPDATE, but its use case might be limited to statements which consist of one foreign table. For this case, we can consider pass-through mode as second way. I think, FDW should run UPDATE or DELETE statement at the scan stage on remote-side, then return a pseudo result to scanner, in case of the statement is enough simple, like no qualifier, no returning, etc... The callback on ExecUpdate/ExecDelete will perform just a stub; that does not actually work except for increment of affected rows. + I have not read your patch closely yet, but I wonder how we can know which column is actually updated. If we have only updated image of tuple, we have to update all remote columns by new values? It seems to me TargetEntry of the parse tree can inform us which column should be modified on UPDATE or INSERT. If it has just a Var element that reference original table as-is, it means here is no change. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] [v9.3] writable foreign tables
Kohei KaiGai wrote: It is a responsibility of FDW extension (and DBA) to ensure each foreign-row has a unique identifier that has 48-bits width integer data type in maximum. For example, if primary key of the remote table is Text data type, an idea is to use a hash table to track the text-formed primary being associated with a particular 48-bits integer. Even if we had a hash collision, each hash entry can have the original key itself to be compared. But anyway, I love the idea to support an opaque pointer to track particular remote-row rather. Me too. Do we have some other reasonable ideas? I'm not certain whether the duration of TupleTableSlot is enough to carry a private datum between scan and modify stage. Is it possible to utilize ctid field to move a private pointer? TID data type is internally represented as a pointer to ItemPointerData, so it has enough width to track an opaque formed remote-row identifier; including string, int64 or others. One disadvantage is ctid system column shows a nonsense value when user explicitly references this system column. But it does not seems to me a fundamental problem, because we didn't give any special meaning on the ctid field of foreign table. I can't say if (ab)using the field that way would cause other problems, but I don't think that nonsense values are a problem. The pointer would stay the same for the duration of the foreign scan, which I think is as good a ctid for a foreign table as anybody should reasonably ask. BTW, I see the following comment in htup.h: * t_self and t_tableOid should be valid if the HeapTupleData points to * a disk buffer, or if it represents a copy of a tuple on disk. They * should be explicitly set invalid in manufactured tuples. I don't know if invalid means zero in that case. Yours, Laurenz Albe -- 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] [v9.3] writable foreign tables
2012/8/28 Albe Laurenz laurenz.a...@wien.gv.at:
Kohei KaiGai wrote:
It is a responsibility of FDW extension (and DBA) to ensure each
foreign-row has a unique identifier that has 48-bits width integer
data type in maximum.
For example, if primary key of the remote table is Text data type,
an idea is to use a hash table to track the text-formed primary
being associated with a particular 48-bits integer.
Even if we had a hash collision, each hash entry can have the original
key itself to be compared. But anyway, I love the idea to support
an opaque pointer to track particular remote-row rather.
Me too.
Do we have some other reasonable ideas?
I'm not certain whether the duration of TupleTableSlot is enough to
carry a private datum between scan and modify stage.
Is it possible to utilize ctid field to move a private pointer?
TID data type is internally represented as a pointer to
ItemPointerData,
so it has enough width to track an opaque formed remote-row
identifier;
including string, int64 or others.
One disadvantage is ctid system column shows a nonsense value
when user explicitly references this system column. But it does not
seems to me a fundamental problem, because we didn't give any
special meaning on the ctid field of foreign table.
I can't say if (ab)using the field that way would cause other
problems, but I don't think that nonsense values are a problem.
The pointer would stay the same for the duration of the foreign
scan, which I think is as good a ctid for a foreign table as
anybody should reasonably ask.
BTW, I see the following comment in htup.h:
* t_self and t_tableOid should be valid if the HeapTupleData points to
* a disk buffer, or if it represents a copy of a tuple on disk. They
* should be explicitly set invalid in manufactured tuples.
I don't know if invalid means zero in that case.
ItemPointerSetInvalid is declared as follows:
/*
* ItemPointerSetInvalid
* Sets a disk item pointer to be invalid.
*/
#define ItemPointerSetInvalid(pointer) \
( \
AssertMacro(PointerIsValid(pointer)), \
BlockIdSet(((pointer)-ip_blkid), InvalidBlockNumber), \
(pointer)-ip_posid = InvalidOffsetNumber \
)
Since ItemPointerGetBlockNumber() and ItemPointerGetOffsetNumber()
checks whether the given ItemPointer is valid, FDWs may have to put
a dummy ItemPointerData on head of their private datum to avoid
the first 6-bytes having zero.
For example, the following data structure is safe to carry an opaque
datum without false-positive of invalid ctid.
typedef struct {
ItemPointerData dumm
char *pk_of_remote_table;
} my_pseudo_rowid;
Thanks,
--
KaiGai Kohei kai...@kaigai.gr.jp
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SP-GiST micro-optimizations
I did some performance testing of building an SP-GiST index, with the
new range type SP-GiST opclass. There's some low-hanging fruit there, I
was able to reduce the index build time on a simple test case by about
20% with a few small changes.
I created a test table with:
create table range_test AS SELECT int4range(i-10, i + 10) as r from
generate_series(1, 10) i;
And measured the time it takes to build an index on that, on my laptop,
by repeating this a few times and taking the lowest value:
\timing
create index i_r on range_test using spgist (r);
On unpatched checkout from master, the shortest time was 19.2 seconds.
Profile taken with 'perf' tool looks like this:
21,43% postmaster postgres [.] spgdoinsert
17,25% postmaster postgres [.] range_deserialize
10,28% postmaster postgres [.] FunctionCall2Coll
9,68% postmaster postgres [.] spgExtractNodeLabels
7,99% postmaster postgres [.] spg_range_quad_choose
7,21% postmaster postgres [.] index_getprocinfo
5,24% postmaster postgres [.] range_cmp_bounds
4,74% postmaster postgres [.] AllocSetAlloc
2,49% postmaster postgres [.] btint4cmp
2,49% postmaster postgres [.] AllocSetFree
1,98% postmaster postgres [.] SpGistGetTypeSize
1,63% postmaster postgres [.] range_get_typcache
1,62% postmaster postgres [.] MemoryContextAlloc
1,16% postmaster postgres [.] pg_detoast_datum
0,87% postmaster postgres [.] PageIndexTupleDelete
0,65% postmaster postgres [.] pfree
0,49% postmaster postgres [.] XLogInsert
Drilling into the profile, I came up with three little optimizations:
1. Within spgdoinsert, a significant portion of the CPU time is spent on
line 2033 in spgdoinsert.c:
memset(out, 0, sizeof(out));
That zeroes out a small struct allocated in the stack. Replacing that
with MemSet() makes it faster, reducing the time spent on zeroing that
struct from 10% to 1.5% of the time spent in spgdoinsert(). That's not
very much in the big scheme of things, but it's a trivial change so
seems worth it.
2. When spgdoinsert descends the tree, it calls index_getprocinfo()
every time it calls the user-defined choose function. By calling it
only once at the beginning of the function, the time spent in that
function drops from 7.21% to 0.02%.
3. Most of the AllocSetAlloc/AllocSetFree calls in the profile are
coming from spgExtractNodeLabels(). It first palloc's an array to hold
node labels, then it iterates through all the nodes in the inner tuple,
and if it turns out that there are no node labels, it pfrees the array
and returns NULL. With this opclass, there never are any node labels, so
spgExtractNodeLabels() always performs a pointless palloc+pfree. By
changing the function to first check if there are node labels, and only
performing the palloc when necessary, we can eliminate the time spent in
AllocSetAlloc and AllocSetFree, about 7% of the CPU time in total.
With those three changes, the profile now looks like this:
22,57% postmaster postgres [.] range_deserialize
21,54% postmaster postgres [.] spgdoinsert
13,37% postmaster postgres [.] FunctionCall2Coll
11,13% postmaster postgres [.] spg_range_quad_choose
7,11% postmaster postgres [.] range_cmp_bounds
6,96% postmaster postgres [.] spgExtractNodeLabels
3,68% postmaster postgres [.] btint4cmp
3,05% postmaster postgres [.] pg_detoast_datum
2,53% postmaster postgres [.] SpGistGetTypeSize
2,47% postmaster postgres [.] range_get_typcache
1,22% postmaster postgres [.] PageIndexTupleDelete
0,66% postmaster postgres [.] XLogInsert
Attached is a patch with those changes. Barring objections, will commit.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
*** a/src/backend/access/spgist/spgdoinsert.c
--- b/src/backend/access/spgist/spgdoinsert.c
***
*** 826,832 doPickSplit(Relation index, SpGistState *state,
heapPtrs[in.nTuples] = newLeafTuple-heapPtr;
in.nTuples++;
! memset(out, 0, sizeof(out));
if (!isNulls)
{
--- 826,832
heapPtrs[in.nTuples] = newLeafTuple-heapPtr;
in.nTuples++;
! MemSet(out, 0, sizeof(out));
if (!isNulls)
{
***
*** 1861,1866 spgdoinsert(Relation index, SpGistState *state,
--- 1861,1874
int leafSize;
SPPageDesc current,
parent;
+ FmgrInfo *procinfo = NULL;
+
+ /*
+ * Look up FmgrInfo of the user-defined choose function once, to save
+ * cycles in the loop below.
+ */
+ if (!isnull)
+ procinfo = index_getprocinfo(index, 1, SPGIST_CHOOSE_PROC);
[HACKERS] pg_dump incorrect output in plaintext mode
$ pg_dump -v postgres -Fc -t t /dev/null ... pg_dump: dumping contents of table t ... but in plaintext: $ pg_dump -v postgres -Fp -t t /dev/null pg_dump: creating TABLE t pg_dump: restoring data for table t pg_dump: dumping contents of table t pg_dump: setting owner and privileges for TABLE t pg_dump: setting owner and privileges for TABLE DATA t In particular, the restoring data is obviously completely wrong. But AFAICT, the creating table and setting owner etc are also wrong... This is because pg_dump calls RestoreArchive(fout). Do we need to pass down a parameter all the way down the functions to tell them if they're restoring or not? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] MySQL search query is not executing in Postgres DB
On Mon, Aug 27, 2012 at 7:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I agree that redefining the lexer behavior is a can of worms. What I don't understand is why f(2+2) can't call f(smallint) when that's the only extant f. It seems to me that we could do that without breaking anything that works today: if you look for candidates and don't find any, try again, allowing assignment casts the second time. Yeah, possibly. Where would you fit that in the existing sequence of tests? http://www.postgresql.org/docs/devel/static/typeconv-func.html I think: If step 4a would result in discarding all candidates, then instead discard candidate functions for which the input types do not match and cannot be converted -- using an ASSIGNMENT conversion -- to match. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Timing overhead and Linux clock sources
On Mon, Aug 27, 2012 at 11:13 PM, Greg Smith g...@2ndquadrant.com wrote: After staring at all the examples I generated again, I think Bruce is right that the newer format he's suggesting is better. OK by me, then. If you're happy, I'm happy. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] [v9.3] writable foreign tables
Kohei KaiGai kai...@kaigai.gr.jp writes: Would it be too invasive to introduce a new pointer in TupleTableSlot that is NULL for anything but virtual tuples from foreign tables? I'm not certain whether the duration of TupleTableSlot is enough to carry a private datum between scan and modify stage. It's not. Is it possible to utilize ctid field to move a private pointer? UPDATEs and DELETEs do not rely on the ctid field of tuples to carry the TID from scan to modify --- in fact, most of the time what the modify step is going to get is a virtual TupleTableSlot that hasn't even *got* a physical CTID field. Instead, the planner arranges for the TID to be carried up as an explicit resjunk column named ctid. (Currently this is done in rewriteTargetListUD(), but see also preptlist.c which does some related things for SELECT FOR UPDATE.) I'm inclined to think that what we need here is for FDWs to be able to modify the details of that behavior, at least to the extent of being able to specify a different data type than TID for the row identification column. 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] [v9.3] writable foreign tables
Kohei KaiGai kai...@kaigai.gr.jp writes: It seems to me TargetEntry of the parse tree can inform us which column should be modified on UPDATE or INSERT. If it has just a Var element that reference original table as-is, it means here is no change. Only if you're not going to support BEFORE triggers modifying the row... 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] [v9.3] writable foreign tables
On Tue, Aug 28, 2012 at 10:58:25AM -0400, Tom Lane wrote: Kohei KaiGai kai...@kaigai.gr.jp writes: It seems to me TargetEntry of the parse tree can inform us which column should be modified on UPDATE or INSERT. If it has just a Var element that reference original table as-is, it means here is no change. Only if you're not going to support BEFORE triggers modifying the row... +1 for supporting these. Speaking of triggers on foreign tables, what's needed to support them independent of support at the FDW level for writing on foreign tables, or does that even make sense? 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] pg_dump incorrect output in plaintext mode
Excerpts from Magnus Hagander's message of mar ago 28 08:08:24 -0400 2012: In particular, the restoring data is obviously completely wrong. But AFAICT, the creating table and setting owner etc are also wrong... This is because pg_dump calls RestoreArchive(fout). Do we need to pass down a parameter all the way down the functions to tell them if they're restoring or not? Hmm, but the info is already there, is it not? Isn't AH-ropt-useDB what you want? As a translator, I'm not sure that I'm too happy with the idea of having to translate double the number of strings in pg_dump ... -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training 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] [v9.3] writable foreign tables
2012/8/28 David Fetter da...@fetter.org: On Tue, Aug 28, 2012 at 10:58:25AM -0400, Tom Lane wrote: Kohei KaiGai kai...@kaigai.gr.jp writes: It seems to me TargetEntry of the parse tree can inform us which column should be modified on UPDATE or INSERT. If it has just a Var element that reference original table as-is, it means here is no change. Only if you're not going to support BEFORE triggers modifying the row... +1 for supporting these. Speaking of triggers on foreign tables, what's needed to support them independent of support at the FDW level for writing on foreign tables, or does that even make sense? I agree with trigger support on foreign tables is definitely useful feature, even though it does not have capability to replace the writable foreign table functionality. In case when foreign-table definition does not contain a column mapped with primary-key column in remote-side, the trigger function cannot determine which row should be updated / deleted. It is a situation that FDW driver should track a particular remote-row using its identifier. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] CREATE SCHEMA IF NOT EXISTS
2012/8/27 Dickson S. Guedes lis...@guedesoft.net [...] Two questions: - Should this patch implements others INEs like ADD COLUMN IF NOT EXISTS? If this feature is important I believe we must implement it. Exists several CREATE statements without IF NOT EXISTS option too, so we can discuss more about it and I can implement it in this patch or in another. - Should pg_dump or pg_restore support some kind of flag to use a CREATE SCHEMA IF NOT EXISTS ... instead CREATE SCHEMA ...? I can't imagine a use case for this feature... can you explain more about your idea? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [v9.3] writable foreign tables
On Tue, Aug 28, 2012 at 05:18:34PM +0200, Kohei KaiGai wrote: 2012/8/28 David Fetter da...@fetter.org: On Tue, Aug 28, 2012 at 10:58:25AM -0400, Tom Lane wrote: Kohei KaiGai kai...@kaigai.gr.jp writes: It seems to me TargetEntry of the parse tree can inform us which column should be modified on UPDATE or INSERT. If it has just a Var element that reference original table as-is, it means here is no change. Only if you're not going to support BEFORE triggers modifying the row... +1 for supporting these. Speaking of triggers on foreign tables, what's needed to support them independent of support at the FDW level for writing on foreign tables, or does that even make sense? I agree with trigger support on foreign tables is definitely useful feature, even though it does not have capability to replace the writable foreign table functionality. With utmost respect, trigger support does make it possible to write to foreign tables using a whole-row comparison with the effect that all whole-row matches would be affected. This is how DBI-Link does it currently. In case when foreign-table definition does not contain a column mapped with primary-key column in remote-side, the trigger function cannot determine which row should be updated / deleted. It is a situation that FDW driver should track a particular remote-row using its identifier. Generated identifiers and whole-row matching are two ways to approach this. There are likely others, especially in cases where people have special knowledge of the remote source. 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] [v9.3] writable foreign tables
2012/8/28 Tom Lane t...@sss.pgh.pa.us: Kohei KaiGai kai...@kaigai.gr.jp writes: Would it be too invasive to introduce a new pointer in TupleTableSlot that is NULL for anything but virtual tuples from foreign tables? I'm not certain whether the duration of TupleTableSlot is enough to carry a private datum between scan and modify stage. It's not. Is it possible to utilize ctid field to move a private pointer? UPDATEs and DELETEs do not rely on the ctid field of tuples to carry the TID from scan to modify --- in fact, most of the time what the modify step is going to get is a virtual TupleTableSlot that hasn't even *got* a physical CTID field. Instead, the planner arranges for the TID to be carried up as an explicit resjunk column named ctid. (Currently this is done in rewriteTargetListUD(), but see also preptlist.c which does some related things for SELECT FOR UPDATE.) I'm inclined to think that what we need here is for FDWs to be able to modify the details of that behavior, at least to the extent of being able to specify a different data type than TID for the row identification column. Hmm. It seems to me a straight-forward solution rather than ab-use of ctid system column. Probably, cstring data type is more suitable to carry a private datum between scan and modify stage. One problem I noticed is how FDW driver returns an extra field that is in neither system nor regular column. Number of columns and its data type are defined with TupleDesc of the target foreign-table, so we also need a feature to extend it on run-time. For example, FDW driver may have to be able to extend a virtual column with cstring data type, even though the target foreign table does not have such a column. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] wal_buffers
On Tue, Aug 28, 2012 at 09:40:33AM +0530, Amit Kapila wrote: From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Bruce Momjian Added to TODO: Allow reporting of stalls due to wal_buffer wrap-around http://archives.postgresql.org/pgsql-hackers/2012-02/msg00826.php Isn't this indicates that while writing XLOG, it needs some tuning such that when some thresh hold buffers(2/3) are full, then trigger LOGWriter. I assumed the LOGWriter was already working as fast as it could, but couldn't keep up. --- On Sun, Feb 19, 2012 at 12:24:12AM -0500, Robert Haas wrote: Just for kicks, I ran two 30-minute pgbench tests at scale factor 300 tonight on Nate Boley's machine, with -n -l -c 32 -j 32. The configurations were identical, except that on one of them, I set wal_buffers=64MB. It seemed to make quite a lot of difference: wal_buffers not set (thus, 16MB): tps = 3162.594605 (including connections establishing) wal_buffers=64MB: tps = 6164.194625 (including connections establishing) Rest of config: shared_buffers = 8GB, maintenance_work_mem = 1GB, synchronous_commit = off, checkpoint_segments = 300, checkpoint_timeout = 15min, checkpoint_completion_target = 0.9, wal_writer_delay = 20ms I have attached tps scatterplots. The obvious conclusion appears to be that, with only 16MB of wal_buffers, the buffer wraps around with some regularity: we can't insert more WAL because the buffer we need to use still contains WAL that hasn't yet been fsync'd, leading to long stalls. More buffer space ameliorates the problem. This is not very surprising, when you think about it: it's clear that the peak tps rate approaches 18k/s on these tests; right after a checkpoint, every update will force a full page write - that is, a WAL record 8kB. So we'll fill up a 16MB WAL segment in about a tenth of a second. That doesn't leave much breathing room. I think we might want to consider adjusting our auto-tuning formula for wal_buffers to allow for a higher cap, although this is obviously not enough data to draw any firm conclusions. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] CREATE SCHEMA IF NOT EXISTS
On Aug 28, 2012, at 8:19 AM, Fabrízio de Royes Mello wrote: - Should this patch implements others INEs like ADD COLUMN IF NOT EXISTS? If this feature is important I believe we must implement it. Exists several CREATE statements without IF NOT EXISTS option too, so we can discuss more about it and I can implement it in this patch or in another. I don't think any of them have to be a part of *this* patch. There can be patches for each of them. Probably simpler that way. But then, I am not like to code them, so WTF do I know? :-) 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] [v9.3] writable foreign tables
2012/8/28 David Fetter da...@fetter.org: On Tue, Aug 28, 2012 at 05:18:34PM +0200, Kohei KaiGai wrote: 2012/8/28 David Fetter da...@fetter.org: On Tue, Aug 28, 2012 at 10:58:25AM -0400, Tom Lane wrote: Kohei KaiGai kai...@kaigai.gr.jp writes: It seems to me TargetEntry of the parse tree can inform us which column should be modified on UPDATE or INSERT. If it has just a Var element that reference original table as-is, it means here is no change. Only if you're not going to support BEFORE triggers modifying the row... +1 for supporting these. Speaking of triggers on foreign tables, what's needed to support them independent of support at the FDW level for writing on foreign tables, or does that even make sense? I agree with trigger support on foreign tables is definitely useful feature, even though it does not have capability to replace the writable foreign table functionality. With utmost respect, trigger support does make it possible to write to foreign tables using a whole-row comparison with the effect that all whole-row matches would be affected. This is how DBI-Link does it currently. In case when foreign-table definition does not contain a column mapped with primary-key column in remote-side, the trigger function cannot determine which row should be updated / deleted. It is a situation that FDW driver should track a particular remote-row using its identifier. Generated identifiers and whole-row matching are two ways to approach this. There are likely others, especially in cases where people have special knowledge of the remote source. One major problem is how to carry the generated identifiers on run-time, even though we have no slot except for system and regular columns defined in TupleDesc of the target foreign tables. It may need a feature to expand TupleDesc on demand. Of course, I don't deny the benefit of trigger support on foreign-tables. Both writable-feature and trigger-support can be supported simultaneously. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] Advisory Lock BIGINT Values
On Aug 27, 2012, at 8:56 PM, Tom Lane wrote: This formula is not actually correct, as you'd soon find out if you experimented with values with the high-order bit of the low-order word set. (Hint: sign extension.) The correct formula is both simpler and far more efficient: (classid::int8 32) | objid::int8 This works because oidtoi8 correctly treats the OID value as unsigned. Oh, nice, thanks! 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] Advisory Lock BIGINT Values
On Mon, Aug 27, 2012 at 11:56:32PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Fri, Mar 9, 2012 at 04:36:08PM -0800, David E. Wheeler wrote: A typebigint/type key is displayed with its high-order half in the structfieldclassid/ column, its low-order half in the structfieldobjid/ column, and structfieldobjsubid/ equal !to 1. The original typebigint/type value can be reassembled with the !expression literal(classid::int::bit(64) lt;lt; 32 | !objid::int::bit(64))::bigint/literal. Integer keys are displayed with the !first key in the structfieldclassid/ column, the second key in the structfieldobjid/ column, and structfieldobjsubid/ equal to 2. The actual meaning of the keys is up to the user. Advisory locks are local to each database, Thanks, applied. This formula is not actually correct, as you'd soon find out if you experimented with values with the high-order bit of the low-order word set. (Hint: sign extension.) The correct formula is both simpler and far more efficient: (classid::int8 32) | objid::int8 This works because oidtoi8 correctly treats the OID value as unsigned. OK, docs updated with attached patch. Thanks. (I used 'bigint' instead of int8 to be consistent with the surrounding text.) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml new file mode 100644 index bf568f1..3bcd82c *** a/doc/src/sgml/catalogs.sgml --- b/doc/src/sgml/catalogs.sgml *** *** 7413,7420 high-order half in the structfieldclassid/ column, its low-order half in the structfieldobjid/ column, and structfieldobjsubid/ equal to 1. The original typebigint/type value can be reassembled with the !expression literal(classid::int::bit(64) lt;lt; 32 | !objid::int::bit(64))::bigint/literal. Integer keys are displayed with the first key in the structfieldclassid/ column, the second key in the structfieldobjid/ column, and structfieldobjsubid/ equal to 2. The actual meaning of --- 7413,7420 high-order half in the structfieldclassid/ column, its low-order half in the structfieldobjid/ column, and structfieldobjsubid/ equal to 1. The original typebigint/type value can be reassembled with the !expression literal(classid::bigint lt;lt; 32) | !objid::bigint/literal. Integer keys are displayed with the first key in the structfieldclassid/ column, the second key in the structfieldobjid/ column, and structfieldobjsubid/ equal to 2. The actual meaning of -- 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] CREATE SCHEMA IF NOT EXISTS
2012/8/28 David E. Wheeler da...@justatheory.com: On Aug 28, 2012, at 8:19 AM, Fabrízio de Royes Mello wrote: - Should this patch implements others INEs like ADD COLUMN IF NOT EXISTS? If this feature is important I believe we must implement it. Exists several CREATE statements without IF NOT EXISTS option too, so we can discuss more about it and I can implement it in this patch or in another. I don't think any of them have to be a part of *this* patch. There can be patches for each of them. Probably simpler that way. Yes, agreed. Other implementations should be in their own distinct patches. BTW, it could be interesting search the archive for past discussions about CINE. I changed the status of this patch to Ready for Committer. Regards, -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype/twitter: guediz ~ github.com/guedes http://guedesoft.net ~ http://www.postgresql.org.br -- 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] [v9.3] writable foreign tables
On Tue, Aug 28, 2012 at 06:08:59PM +0200, Kohei KaiGai wrote: 2012/8/28 David Fetter da...@fetter.org: On Tue, Aug 28, 2012 at 05:18:34PM +0200, Kohei KaiGai wrote: 2012/8/28 David Fetter da...@fetter.org: On Tue, Aug 28, 2012 at 10:58:25AM -0400, Tom Lane wrote: Kohei KaiGai kai...@kaigai.gr.jp writes: It seems to me TargetEntry of the parse tree can inform us which column should be modified on UPDATE or INSERT. If it has just a Var element that reference original table as-is, it means here is no change. Only if you're not going to support BEFORE triggers modifying the row... +1 for supporting these. Generated identifiers and whole-row matching are two ways to approach this. There are likely others, especially in cases where people have special knowledge of the remote source. One major problem is how to carry the generated identifiers on run-time, even though we have no slot except for system and regular columns defined in TupleDesc of the target foreign tables. It may need a feature to expand TupleDesc on demand. Could be. You know a lot more about the implementation details than I do. Of course, I don't deny the benefit of trigger support on foreign-tables. Both writable-feature and trigger-support can be supported simultaneously. Do you see these as independent features, or is there some essential overlap? 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
[HACKERS] Use of systable_beginscan_ordered in event trigger patch
I find $SUBJECT fairly scary, because systable_beginscan_ordered() is dependent on having a working, non-corrupt index. If you are trying to run the backend with ignore_system_indexes so that you can rebuild corrupt indexes, uses of systable_beginscan_ordered() represent places where you can't turn that off, and are entirely at the mercy of the indexes being good. Accordingly, that function isn't supposed to be used in any places where you cannot avoid its use during recovery of core system indexes. I am not sure to what extent its use in the TOAST support compromises that position, but for sure the fact that it's called from EventTriggerDDLCommandStart has broken the concept completely. If pg_event_trigger_evtname_index becomes corrupt, you can kiss your database goodbye, because you have no hope whatsoever of issuing the commands needed to reindex it. Maybe it's time to bite the bullet and implement a heapscan-and-sort code path for systable_beginscan_ordered to use when ignore_system_indexes is set. But that's a fair amount of work. The path of least resistance would be to make the event trigger stuff not depend on this function. Or maybe we should disable event triggers altogether in standalone mode? I can think of plenty of ways that a broken event trigger could cause enough havoc that you'd wish there was a way to suppress it, at least for long enough to drop it again. 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] pg_dump incorrect output in plaintext mode
Magnus Hagander mag...@hagander.net writes: but in plaintext: $ pg_dump -v postgres -Fp -t t /dev/null pg_dump: creating TABLE t pg_dump: restoring data for table t pg_dump: dumping contents of table t pg_dump: setting owner and privileges for TABLE t pg_dump: setting owner and privileges for TABLE DATA t I don't see anything particularly incorrect about that. The point of the --verbose switch is to track what pg_dump is doing, and if what it's doing involves going through RestoreArchive(), why should we try to hide the fact? 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] Use of systable_beginscan_ordered in event trigger patch
On Tuesday, August 28, 2012 06:39:50 PM Tom Lane wrote: Or maybe we should disable event triggers altogether in standalone mode? I can think of plenty of ways that a broken event trigger could cause enough havoc that you'd wish there was a way to suppress it, at least for long enough to drop it again. +1 Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training 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] MySQL search query is not executing in Postgres DB
On Mon, Aug 27, 2012 at 10:03 PM, Robert Haas robertmh...@gmail.com wrote: We really ought to put some effort into solving this problem. I've seen a few Oracle-migration talks at conferences, and *every one* of them has mentioned the smallint problem. It hits our customers, too. I'm kind of puzzled how Oracle-migration talks talk about a smallint problem given that Oracle only has NUMBER which is a variable-sized data type. Why would Oracle people end up with an f(smallint) at all? Perhaps just a warning on CREATE FUNCTION when one of the arguments doesn't have an implicit cast from the canonical data type of that hierarchy saying perhaps you should consider using that data type and let Postgres convert instead of the more specific data type? -- greg -- 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] psql: tab-completion fails SET var=
On Fri, Mar 30, 2012 at 08:15:22PM +0200, Erik Rijkers wrote:
(in hopes that the current changes to tab-completion will help to get this
fixed)
tab-completion goes wrong on SET setting=...
example:
If you want to input set search_path=myschema; without spaces around '=',
and you try tab-completion halfway the schemaname:
set search_path=mysch
^tab
then the initial part of the schema name ('mysch') gets removed and replaced
with 'TO'.
So that you now have:
set search_path=TO cursor
You lose the part of the schema name that was already input.
With spaces that doesn't happen, but after all tabcompletion is about
avoiding keystrokes and errors.
Tab-completion is great and this search_path-annoyance happens to me all the
time; my fingers
can't seem to learn this exception.
(pgsql 9.2devel, problem also in latest 9.1.3)
I have fixed this with the attached patch. It does not do any further
competion of DEFAULT or other known settings if the equal sign has no
space before it.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
new file mode 100644
index 8a74877..bfba1dd
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
*** psql_completion(char *text, int start, i
*** 2842,2847
--- 2842,2848
pg_strcasecmp(prev_wd, TABLESPACE) != 0
pg_strcasecmp(prev_wd, SCHEMA) != 0
prev_wd[strlen(prev_wd) - 1] != ')'
+ prev_wd[strlen(prev_wd) - 1] != '='
pg_strcasecmp(prev4_wd, DOMAIN) != 0)
COMPLETE_WITH_CONST(TO);
/* Suggest possible variable values */
--
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] Timing overhead and Linux clock sources
On Tue, Aug 28, 2012 at 10:43:07AM -0400, Robert Haas wrote: On Mon, Aug 27, 2012 at 11:13 PM, Greg Smith g...@2ndquadrant.com wrote: After staring at all the examples I generated again, I think Bruce is right that the newer format he's suggesting is better. OK by me, then. If you're happy, I'm happy. OK, applied. The general rule I followed here is that if a report has a fixed start value and a variable stop value, the start value should be reported first. When comparing runs, any missing (ending) rows are seen visually as empty (at the end of the output but blank). This isn't a common case, so this issue doesn't come up often. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] MySQL search query is not executing in Postgres DB
Greg Stark st...@mit.edu writes: Perhaps just a warning on CREATE FUNCTION when one of the arguments doesn't have an implicit cast from the canonical data type of that hierarchy saying perhaps you should consider using that data type and let Postgres convert instead of the more specific data type? This might be a good idea if we could write such a test in a principled way, but I'm not seeing how. We don't really have a concept of canonical data types. Also, right at the moment it's not clear to me whether there are any other cases besides integer literal vs smallint argument. I think that's the only particularly surprising case within the numeric hierarchy --- and for non-numeric types, the literal is generally going to start out unknown so the whole problem doesn't arise. I feel uncomfortable trying to invent general-purpose solutions to problems we have only one instance of ... 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] default deny for roles
Folks, There are situations where a default deny policy is the best fit. To that end, I have a modest proposal: REVOKE PUBLIC FROM role; Thenceforth, the role in question would only have access to things it was specifically granted. What say? 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] SP-GiST micro-optimizations
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Drilling into the profile, I came up with three little optimizations: 1. Within spgdoinsert, a significant portion of the CPU time is spent on line 2033 in spgdoinsert.c: memset(out, 0, sizeof(out)); That zeroes out a small struct allocated in the stack. Replacing that with MemSet() makes it faster, reducing the time spent on zeroing that struct from 10% to 1.5% of the time spent in spgdoinsert(). That's not very much in the big scheme of things, but it's a trivial change so seems worth it. Fascinating. I'd been of the opinion that modern compilers would inline memset() for themselves and MemSet was probably not better than what the compiler could do these days. What platform are you testing on? The other two changes seem reasonable. 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] MySQL search query is not executing in Postgres DB
On Tue, Aug 28, 2012 at 1:06 PM, Tom Lane t...@sss.pgh.pa.us wrote: Also, right at the moment it's not clear to me whether there are any other cases besides integer literal vs smallint argument. I think that's the only particularly surprising case within the numeric hierarchy --- and for non-numeric types, the literal is generally going to start out unknown so the whole problem doesn't arise. I feel uncomfortable trying to invent general-purpose solutions to problems we have only one instance of ... The other case that comes up regularly is someone trying to pass some kind of number to a function such as LPAD(). There is only one LPAD() so no ambiguity exists, but PostgreSQL doesn't even see that there's a candidate. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Use of systable_beginscan_ordered in event trigger patch
On Tue, Aug 28, 2012 at 12:47 PM, Andres Freund and...@2ndquadrant.com wrote: On Tuesday, August 28, 2012 06:39:50 PM Tom Lane wrote: Or maybe we should disable event triggers altogether in standalone mode? I can think of plenty of ways that a broken event trigger could cause enough havoc that you'd wish there was a way to suppress it, at least for long enough to drop it again. +1 +1. I initially suggested a PGC_SUSET GUC to disable event triggers, and I'm still not entirely convinced that we shouldn't have one. Maybe we could just force it to disabled in standalone mode. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] MySQL search query is not executing in Postgres DB
Robert Haas robertmh...@gmail.com writes: On Tue, Aug 28, 2012 at 1:06 PM, Tom Lane t...@sss.pgh.pa.us wrote: Also, right at the moment it's not clear to me whether there are any other cases besides integer literal vs smallint argument. I think that's the only particularly surprising case within the numeric hierarchy --- and for non-numeric types, the literal is generally going to start out unknown so the whole problem doesn't arise. I feel uncomfortable trying to invent general-purpose solutions to problems we have only one instance of ... The other case that comes up regularly is someone trying to pass some kind of number to a function such as LPAD(). There is only one LPAD() so no ambiguity exists, but PostgreSQL doesn't even see that there's a candidate. There still won't be a candidate for that one, unless you're proposing to allow explicit-only coercions to be applied implicitly. 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] MySQL search query is not executing in Postgres DB
On 8/27/12 5:19 PM, Greg Sabino Mullane wrote: Tom Lane replied: Come on, really? Note that the above example works without casts if you use int*or* bigint*or* numeric, but not smallint. That could be fixed by causing sufficiently-small integers to lex as smallints, Is there any general interest in adjusting smallint casting? ... It's conceivable that a change in the lexer behavior combined with a massive reorganization of the integer-related operators would bring us to a nicer place than where we are now. But it'd be a lot of work for dubious reward, and it would almost certainly generate a pile of application compatibility problems. Okay, but what about a more targeted solution to the original poster's problem? That seems doable without causing major breakage elsewhere FWIW, this causes problems for me at work as well. For the case of const op const instead of trying to small-cast the 2 constants, would it be possible to large-cast them, perform the operation, and then re-cast the results of the operation? ISTM that would solve the operator issue (ie, the case of 2+2). -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] default deny for roles
David Fetter da...@fetter.org writes: There are situations where a default deny policy is the best fit. To that end, I have a modest proposal: REVOKE PUBLIC FROM role; Neither possible nor sensible. PUBLIC means everybody, and is implemented in a way that doesn't allow any other meaning. We pretty much have default deny at the other end anyway, in that most types of objects start out without any permissions granted to PUBLIC. So I don't think you've made an adequate (or indeed any) case for needing this, even if it were redesigned into something less screwy. 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] default deny for roles
David, * David Fetter (da...@fetter.org) wrote: There are situations where a default deny policy is the best fit. That's certainly true. It's also what we *have*. The only places where we aren't default deny are places where things have been granted to public. Feel free to revoke public from the objects in your environment. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] SP-GiST micro-optimizations
On 28.08.2012 20:30, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: Drilling into the profile, I came up with three little optimizations: 1. Within spgdoinsert, a significant portion of the CPU time is spent on line 2033 in spgdoinsert.c: memset(out, 0, sizeof(out)); That zeroes out a small struct allocated in the stack. Replacing that with MemSet() makes it faster, reducing the time spent on zeroing that struct from 10% to 1.5% of the time spent in spgdoinsert(). That's not very much in the big scheme of things, but it's a trivial change so seems worth it. Fascinating. I'd been of the opinion that modern compilers would inline memset() for themselves and MemSet was probably not better than what the compiler could do these days. What platform are you testing on? x64, gcc 4.7.1, running Debian. The assembly generated for the MemSet is: .loc 1 2033 0 discriminator 3 movq$0, -432(%rbp) .LVL166: movq$0, -424(%rbp) .LVL167: movq$0, -416(%rbp) .LVL168: movq$0, -408(%rbp) .LVL169: movq$0, -400(%rbp) .LVL170: movq$0, -392(%rbp) while the corresponding memset code is: .loc 1 2040 0 discriminator 6 xorl%eax, %eax .loc 1 2042 0 discriminator 6 cmpb$0, -669(%rbp) .loc 1 2040 0 discriminator 6 movq-584(%rbp), %rdi movl$6, %ecx rep stosq In fact, with -mstringop=unrolled_loop, I can coerce gcc to produce code similar to the MemSet version: movq%rax, -440(%rbp) .loc 1 2040 0 discriminator 6 xorl%eax, %eax .L254: movl%eax, %edx addl$32, %eax cmpl$32, %eax movq$0, -432(%rbp,%rdx) movq$0, -424(%rbp,%rdx) movq$0, -416(%rbp,%rdx) movq$0, -408(%rbp,%rdx) jb .L254 leaq-432(%rbp), %r9 addq%r9, %rax .loc 1 2042 0 discriminator 6 cmpb$0, -665(%rbp) .loc 1 2040 0 discriminator 6 movq$0, (%rax) movq$0, 8(%rax) I'm not sure why gcc doesn't choose that by default. Perhaps it's CPU specific which variant is faster - I was quite surprised that MemSet was such a clear win on my laptop. Or maybe it's a speed-space tradeoff, and gcc chooses the more compact version, although using -O3 instead of -O2 made no difference. -- 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] MySQL search query is not executing in Postgres DB
On Tue, Aug 28, 2012 at 1:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: The other case that comes up regularly is someone trying to pass some kind of number to a function such as LPAD(). There is only one LPAD() so no ambiguity exists, but PostgreSQL doesn't even see that there's a candidate. There still won't be a candidate for that one, unless you're proposing to allow explicit-only coercions to be applied implicitly. OK, I'm confused. rhaas=# create table foo (a text); CREATE TABLE rhaas=# insert into foo values (12345); INSERT 0 1 There are no pg_cast entries for integer - text, but this still gets treated as an assignment cast because of some special-case logic in find_coercion_pathway(). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] SP-GiST micro-optimizations
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 28.08.2012 20:30, Tom Lane wrote: Fascinating. I'd been of the opinion that modern compilers would inline memset() for themselves and MemSet was probably not better than what the compiler could do these days. What platform are you testing on? x64, gcc 4.7.1, running Debian. The assembly generated for the MemSet is: [ pretty darn tight ] while the corresponding memset code is: [ not so good ] Seems like that's down to the CPU not doing rep stosq particularly quickly, which might well be chip-specific. Anyway, IIRC there are similar memsets for all the SPGiST opclass invocation calls, so I guess you should switch them all not just these two. 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] MySQL search query is not executing in Postgres DB
Robert Haas robertmh...@gmail.com writes: On Tue, Aug 28, 2012 at 1:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: There still won't be a candidate for that one, unless you're proposing to allow explicit-only coercions to be applied implicitly. [ not so, see kluge in find_coercion_pathway() ] Oh, I'd forgotten that worked that way. Frankly, that makes me quite a bit more concerned about this proposal than I was before. I do *not* want to re-introduce silent cross-category casts to text, not even if there's no other way to match the function/operator. I think that hack was/is tolerable for actual assignment to a table column, because there is very little chance that the semantics of such an assignment will come out differently than the user expected. This is not the case when you're matching to potentially overloaded functions or operators, though. If we go down this route we're going to find ourselves back in the badlands of timestamps sometimes being compared as though they were strings, and all the other sillinesses that we got rid of in 8.3. I got beat up enough already for taking those toys away from people; I'm not looking forward to having to have another round of it in the future. I could see doing what you suggest as long as we exclude the automatic-coerce-via-IO case. 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] FATAL: bogus data in lock file postmaster.pid:
On Mon, Aug 27, 2012 at 10:17:43PM -0400, Bruce Momjian wrote:
Seems pg_ctl would also need some cleanup if we change the error
message and/or timing.
I am thinking we should just change the error message in the postmaster
and pg_ctl to say the file is empty, and call it done (no hint message).
If we do want a hint, say that either the file is stale from a crash or
another postmaster is starting up, and let the user diagnose it.
Updated patch attached which just reports the file as empty. I assume
we don't want the extra text output for pg_ctl like we do for the
backend.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
new file mode 100644
index 775d71f..9f0a58a
*** a/src/backend/utils/init/miscinit.c
--- b/src/backend/utils/init/miscinit.c
*** CreateLockFile(const char *filename, boo
*** 766,771
--- 766,781
filename)));
close(fd);
+ if (len == 0)
+ {
+ ereport(FATAL,
+ (errcode(ERRCODE_LOCK_FILE_EXISTS),
+ errmsg(lock file \%s\ is empty, filename),
+ errhint(
+ Either another server is starting, or the lock file is the remnant\n
+ of a previous server startup crash.)));
+ }
+
buffer[len] = '\0';
encoded_pid = atoi(buffer);
diff --git a/src/bin/pg_ctl/pg_ctl.c b/src/bin/pg_ctl/pg_ctl.c
new file mode 100644
index af8d8b2..0be0f2d
*** a/src/bin/pg_ctl/pg_ctl.c
--- b/src/bin/pg_ctl/pg_ctl.c
*** get_pgpid(void)
*** 292,299
}
if (fscanf(pidf, %ld, pid) != 1)
{
! write_stderr(_(%s: invalid data in PID file \%s\\n),
progname, pid_file);
exit(1);
}
fclose(pidf);
--- 292,304
}
if (fscanf(pidf, %ld, pid) != 1)
{
! /* Is the file empty? */
! if (feof(pidf))
! write_stderr(_(%s: the PID file \%s\ is empty\n),
progname, pid_file);
+ else
+ write_stderr(_(%s: invalid data in PID file \%s\\n),
+ progname, pid_file);
exit(1);
}
fclose(pidf);
--
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_dump incorrect output in plaintext mode
On Tue, Aug 28, 2012 at 6:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: but in plaintext: $ pg_dump -v postgres -Fp -t t /dev/null pg_dump: creating TABLE t pg_dump: restoring data for table t pg_dump: dumping contents of table t pg_dump: setting owner and privileges for TABLE t pg_dump: setting owner and privileges for TABLE DATA t I don't see anything particularly incorrect about that. The point of the --verbose switch is to track what pg_dump is doing, and if what it's doing involves going through RestoreArchive(), why should we try to hide the fact? restoring data for table 't' makes you think it's actuall restoring things. It's not. That dumping is implemented by calling an internal function called RestoreArchive() has to be an implementation detail... It certainly confuses users that we say restoring when we're not doing that... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Audit Logs WAS: temporal support patch
On 8/22/12 3:03 AM, Pavel Stehule wrote: SELECT coverage_amt FROM policy FOR SYSTEM_TIME AS OF '2010-12-01' WHERE id = ; SELECT count(*) FROM policy FOR SYSTEM_TIME FROM '2011-11-30' TO '-12-30' WHERE vin = 'A'; I like this design - it is simple without other objects The thing I don't like about this is it assumes that time is the best way to refer to when things changed in a system. Not only is that a bad assumption, it also means that relating things to history becomes messy. The concept that we promote at work is that if you're going to version something (I don't like the term history because it implies you only want a logfile), you should have an explicit way to refer to any given version. So if you want to track the versioning of a specific field on a table: CREATE TABLE customer_status_versions ( customer_status_version_id SERIAL , customer_id , previous_customer_status_version_id REFERENCES customer_status_versions , changed_at timestamptz , new_customer_status ); That kind of structure makes it impossible to be ambiguous about the ordering of changes to a single customer's status. It also means that you have a specific identifier you can use in places of the system that care about that. IE: CREATE TABLE loans( ... , customer_id , customer_status_when_issued REFERENCES customer_status_versions ); Now, when you look at a loan there is *zero* question on not only what the customer's status was when the loan was issued. Not only that, you can absolutely reliably know all customer status changes that had taken place up to that point. And you can do this without any complex temporal logic or reliance on a system clock that might not be reliable. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] SP-GiST micro-optimizations
On Tue, Aug 28, 2012 at 9:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Seems like that's down to the CPU not doing rep stosq particularly quickly, which might well be chip-specific. AMD optimization manual[1] states the following: For repeat counts of less than 4k, expand REP string instructions into equivalent sequences of simple AMD64 instructions. Intel optimization manual[2] doesn't provide equivalent guidelines, but the graph associated with string instructions states about 30 cycles of startup latency. The mov based code on the other hand executes in 6 cycles and can easily overlap with other non-store instructions. [1] http://support.amd.com/us/Processor_TechDocs/25112.PDF [2] http://www.intel.com/content/dam/doc/manual/64-ia-32-architectures-optimization-manual.pdf Ants Aasma -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- 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] Audit Logs WAS: temporal support patch
2012/8/28 Jim Nasby j...@nasby.net: On 8/22/12 3:03 AM, Pavel Stehule wrote: SELECT coverage_amt FROM policy FOR SYSTEM_TIME AS OF '2010-12-01' WHERE id = ; SELECT count(*) FROM policy FOR SYSTEM_TIME FROM '2011-11-30' TO '-12-30' WHERE vin = 'A'; I like this design - it is simple without other objects The thing I don't like about this is it assumes that time is the best way to refer to when things changed in a system. Not only is that a bad assumption, it also means that relating things to history becomes messy. On second hand I don't have a problem with some optional counter, although I think so database system time is very useful and other counters for versioning are not necessary - because in one time I can have only one version - it doesn't do versions from rollbacked transactions. The concept that we promote at work is that if you're going to version something (I don't like the term history because it implies you only want a logfile), you should have an explicit way to refer to any given version. So if you want to track the versioning of a specific field on a table: CREATE TABLE customer_status_versions ( customer_status_version_id SERIAL , customer_id , previous_customer_status_version_id REFERENCES customer_status_versions , changed_at timestamptz , new_customer_status ); That kind of structure makes it impossible to be ambiguous about the ordering of changes to a single customer's status. It also means that you have a specific identifier you can use in places of the system that care about that. IE: CREATE TABLE loans( ... , customer_id , customer_status_when_issued REFERENCES customer_status_versions ); Now, when you look at a loan there is *zero* question on not only what the customer's status was when the loan was issued. Not only that, you can absolutely reliably know all customer status changes that had taken place up to that point. And you can do this without any complex temporal logic or reliance on a system clock that might not be reliable. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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_dump incorrect output in plaintext mode
Magnus Hagander mag...@hagander.net writes: On Tue, Aug 28, 2012 at 6:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: I don't see anything particularly incorrect about that. The point of the --verbose switch is to track what pg_dump is doing, and if what it's doing involves going through RestoreArchive(), why should we try to hide the fact? restoring data for table 't' makes you think it's actuall restoring things. It's not. That dumping is implemented by calling an internal function called RestoreArchive() has to be an implementation detail... It certainly confuses users that we say restoring when we're not doing that... Well, why don't we just s/restoring/processing/ in the debug message, and call it good? 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] FATAL: bogus data in lock file postmaster.pid:
Bruce Momjian br...@momjian.us writes: Updated patch attached which just reports the file as empty. I assume we don't want the extra text output for pg_ctl like we do for the backend. The backend side of this looks mostly sane to me (but drop the \n, messages are not supposed to contain those). But the feof test proposed for pg_ctl is no good: consider a file containing just, say, -. fscanf would eat the -, then hit eof, and this would complain the file is empty. Possibly checking for ftell(pidf) == 0 would do, though I'm not sure whether it's portable to assume fscanf would eat a non-numeric character before complaining. 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] MySQL search query is not executing in Postgres DB
On Tue, Aug 28, 2012 at 2:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Oh, I'd forgotten that worked that way. Frankly, that makes me quite a bit more concerned about this proposal than I was before. I do *not* want to re-introduce silent cross-category casts to text, not even if there's no other way to match the function/operator. I think that hack was/is tolerable for actual assignment to a table column, because there is very little chance that the semantics of such an assignment will come out differently than the user expected. Well, I think that when there is only one LPAD function, there is also very little chance that the results will come out differently than the user expected. I'm having a hard time seeing a bright line between those two cases. Remember, I'm not proposing that we try to guess between more alternatives than we're already trying to guess between - only that we do something other than fail outright in situations where we currently do. The changes we made in 8.3 broke a bunch of cases that were actually ambiguous. That was painful, but probably for the best. What wasn't, in my opinion, for the best was that we also broke a lot of cases - including this one - that were by no means ambiguous. In fact, I believe that every place that I had to fix my application code actually fell into the latter category: there was no actual ambiguity, but I had to go back and insert a cast anyway. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Incorrect behaviour when using a GiST index on points
On Mon, Aug 27, 2012 at 7:43 PM, Tom Lane t...@sss.pgh.pa.us wrote: There's also the big-picture question of whether we should just get rid of fuzzy comparisons in the geometric types instead of trying to hack indexes to work around them. +1 for that approach, but only if I don't have to do the work. Otherwise, +1 for doing the simplest thing that we're sure will eliminate wrong answers. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] MySQL search query is not executing in Postgres DB
Robert Haas robertmh...@gmail.com writes: On Tue, Aug 28, 2012 at 2:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Oh, I'd forgotten that worked that way. Frankly, that makes me quite a bit more concerned about this proposal than I was before. I do *not* want to re-introduce silent cross-category casts to text, not even if there's no other way to match the function/operator. I think that hack was/is tolerable for actual assignment to a table column, because there is very little chance that the semantics of such an assignment will come out differently than the user expected. Well, I think that when there is only one LPAD function, there is also very little chance that the results will come out differently than the user expected. [ shrug... ] I'm having a hard time resisting the temptation to point out that there are two. The real point here though is that the proposed behavior change will affect all functions, not only the cases where you think there is only one sane behavior. And features such as search paths and default parameters frequently mean that there are more potential matches than the user thought of while writing the query. In the end, SQL is a fairly strongly typed language, especially in our manifestation of it. I don't think we should give that up, especially not for benefits as dubious as not having to write a cast to make it clear that yes you really do want a timestamp to be treated as text. IMO, saving people from the errors that inevitably arise from that sort of sloppy thinking is a benefit, not a cost, of having a typed language. 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] temporal support patch
On 8/27/12 12:40 PM, Robert Haas wrote: On Sat, Aug 25, 2012 at 1:30 PM, David Johnstonpol...@yahoo.com wrote: My internals knowledge is basically zero but it would seem that If you simply wanted the end-of-transaction result you could just record nothing during the transaction and then copy whatever values are present at commit to whatever logging mechanism you need. Whatever values are present and commit could be a terabyte of data. Or it could be a kilobyte of changed data within a terabyte database. You'd need some way to identify which data actually needs to be copied, since you surely don't want to copy the whole database. And even if you can identify it, going back and visiting all those blocks a second time will be expensive. This makes me think about the original time travel, which was to not automatically remove old tuple versions. I think it would be interesting to look at allowing old tuples to be visible as well as doing something different when vacuum comes around. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] Incorrect behaviour when using a GiST index on points
Robert Haas robertmh...@gmail.com writes: On Mon, Aug 27, 2012 at 7:43 PM, Tom Lane t...@sss.pgh.pa.us wrote: There's also the big-picture question of whether we should just get rid of fuzzy comparisons in the geometric types instead of trying to hack indexes to work around them. +1 for that approach, but only if I don't have to do the work. Otherwise, +1 for doing the simplest thing that we're sure will eliminate wrong answers. What we're forced to speculate about here is how many applications out there are relying on fuzzy comparison to get answers they like, versus how many are getting answers they don't like because of it. The fact that the underlying storage is float8 not numeric suggests there are probably some cases where fuzzy is helpful. Another issue here is that even if we agree that simple comparisons (operator complexity up to about the limit of what an index might support) should be exact, there's something to be said for fuzzy computations for operators like whether a point falls on a line. Internal roundoff error makes that problematic even if you assume that the inputs are exact. I've never cared for the particulars of the way the fuzzy comparisons are done, in any case: using an absolute rather than relative error threshold is wrong according to every numerical analysis principle I know. The long and the short of it is that it will probably take a significant investment of work to make something that's clearly better. If that weren't the case, we'd have done something long ago. 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] Audit Logs WAS: temporal support patch
On 8/28/12 2:51 PM, Pavel Stehule wrote: The thing I don't like about this is it assumes that time is the best way to refer to when things changed in a system. Not only is that a bad assumption, it also means that relating things to history becomes messy. On second hand I don't have a problem with some optional counter, although I think so database system time is very useful and other counters for versioning are not necessary - because in one time I can have only one version - it doesn't do versions from rollbacked transactions. What happens if the system clock runs backwards? What happens if two transactions start in the same microsecond? (And I know for a fact that's possible, because I've seen it). More importantly, I believe using time to handle recording a versioned history of something is flawed to begin with. You might care about what time a new version was created; but what's far more important is recording the correct ordering of things, and time isn't actually a great way to do that. Note that no version control systems use time as their primary attribute. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] splitting htup.h
Alvaro Herrera alvhe...@2ndquadrant.com writes: This patch is mainly doing four things: 1. take some typedefs and the HeapTupleData struct definition from access/htup.h, and put them in access/tupbasics.h. This new file is used as #include in all headers instead of htup.h. I'm unsure about the tupbasics.h file name. I'm open to better ideas. I'd be inclined to keep the name htup.h for the more widely used file, and invent a new name for what we're splitting out of it. This should reduce the number of changes needed, not only in our code but third party code. Not sure if the new file could sanely be called htup_private.h; it seems a bit widely used for that. Maybe heap.h? Also, is there any reason to consider just moving those defs into heapam.h, instead of inventing a new header? I'm not sure if there's any principled distinction between heap.h and heapam.h, or any significant differences between their sets of consumers. The other changes all sound sane. 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] Audit Logs WAS: temporal support patch
On Tue, Aug 28, 2012 at 5:06 PM, Jim Nasby j...@nasby.net wrote: On 8/28/12 2:51 PM, Pavel Stehule wrote: The thing I don't like about this is it assumes that time is the best way to refer to when things changed in a system. Not only is that a bad assumption, it also means that relating things to history becomes messy. On second hand I don't have a problem with some optional counter, although I think so database system time is very useful and other counters for versioning are not necessary - because in one time I can have only one version - it doesn't do versions from rollbacked transactions. What happens if the system clock runs backwards? What happens if two transactions start in the same microsecond? (And I know for a fact that's possible, because I've seen it). More importantly, I believe using time to handle recording a versioned history of something is flawed to begin with. You might care about what time a new version was created; but what's far more important is recording the correct ordering of things, and time isn't actually a great way to do that. Note that no version control systems use time as their primary attribute. At one point, I designed a configuration system that used the DNS concept of serial numbers to indicate the temporality of the configuration. NULL was treated as 'infinitely in the future'. The notion would be that a new piece of configuration would initially be assigned (latest_serial+1, NULL) as its temporal visibility, and, upon bumping the serial number, that would become live configuration. One would mark a piece of configuration as about to die by assigning (X, latest_serial+1) as its temporal visibility, and, again, upon bumping the serial number, that terminates its visibility. In that approach to things, it's an expensive serialization event to bump the serial number. Not that the action is super-expensive, it's just that that's not something you ought to do in a distributed fashion. ONE process should bump the serial number. I think you're right that for these sorts of cases, the use of time as the source of versioning information is pretty dangerous. In SCM systems, we discovered that it was pretty horrible to try to assign serial numbers in a 'consistent' fashion; you'd get, in CVS, that files would get assigned version '1.2.1.5.3.2.7', and things would get worse from there. It turned out that using a hash code like SHA-* as a version number was more satisfactory; just attach labels to those versions that you care to identify. It's possible that, for a versioned history, that assigning a hash/UUID as the version ID is pretty satisfactory, even though it's not ordered. Using a sequence to assign a version scales a bit less well, but is probably usually OK. One of the other things discovered in that config system design was that it was mighty useful to assign transactions at will: create table config_txns ( tx_id serial primary key, tx_at timestamptz default now() ); , associating data with the tx_id value, and then associate additional information to the config_txns table if needful. For instance, sometimes you need a label (akin to an SCM branch or tag label) create table config_labels ( tx_id integer primary key references config_txns(tx_id) on delete cascade, label text not null unique ); -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- 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] default deny for roles
On 08/29/2012 01:25 AM, David Fetter wrote: Folks, There are situations where a default deny policy is the best fit. To that end, I have a modest proposal: REVOKE PUBLIC FROM role; Thenceforth, the role in question would only have access to things it was specifically granted. Wouldn't that render the user utterly unable to do anything until you added a bunch of GRANTs on the system catalogs for that user or a group they're a member of? Is that the idea? To restrict system catalog access? You'd have to GRANT every function, every INFORMATION_SCHEMA and pg_catalog entry, etc. Is that really your intention? All public gets by default is: - CONNECT, TEMPORARY on databases - USAGE on trusted PLs - USAGE on schema - EXECUTE on functions as per http://www.postgresql.org/docs/9.1/static/sql-grant.html: Depending on the type of object, the initial default privileges might include granting some privileges to PUBLIC. The default is no public access for tables, columns, schemas, and tablespaces; CONNECT privilege and TEMP table creation privilege for databases; EXECUTE privilege for functions; and USAGE privilege for languages. The object owner can of course revoke these privileges. This *doesn't* mention the system catalogs, which it perhaps should, but otherwise makes it pretty clear that `public` doesn't get to do much. -- Craig Ringer -- 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] 64-bit API for large object
Correct me if I am wrong. After expanding large object API to 64-bit, the max size of a large object will be 8TB(assuming 8KB default BLKSZ). large object max size = pageno(int32) * LOBLKSIZE = (2^32-1) * (BLCKSZ / 4) = (2^32-1) * (8192/4) = 8TB I just want to confirm my calculation is correct. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] default deny for roles
On 08/28/2012 09:09 PM, Craig Ringer wrote: On 08/29/2012 01:25 AM, David Fetter wrote: Folks, There are situations where a default deny policy is the best fit. To that end, I have a modest proposal: REVOKE PUBLIC FROM role; Thenceforth, the role in question would only have access to things it was specifically granted. Wouldn't that render the user utterly unable to do anything until you added a bunch of GRANTs on the system catalogs for that user or a group they're a member of? No. Try it and see. You can do a lot without having any access rights at all to the catalog tables. 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] MySQL search query is not executing in Postgres DB
On 08/29/2012 01:32 AM, Robert Haas wrote: On Tue, Aug 28, 2012 at 1:06 PM, Tom Lane t...@sss.pgh.pa.us wrote: Also, right at the moment it's not clear to me whether there are any other cases besides integer literal vs smallint argument. I think that's the only particularly surprising case within the numeric hierarchy --- and for non-numeric types, the literal is generally going to start out unknown so the whole problem doesn't arise. I feel uncomfortable trying to invent general-purpose solutions to problems we have only one instance of ... The other case that comes up regularly is someone trying to pass some kind of number to a function such as LPAD(). There is only one LPAD() so no ambiguity exists, but PostgreSQL doesn't even see that there's a candidate. Allowing Pg to assign parameters or fields by using the normally-only-explicit casts where no ambiguity exists would be *really* helpful in other areas, too. In particular, this applies with assignment of fields from `text' input, too. PostgreSQL can be incredibly frustrating to work with from Java/JDBC where everything goes through protocol-level parameterised statements, because you can't use Java `String' types via PreparedStatement.setString() to assign to, say, an `xml' or `json' field, you have to use `setObject()'. That's OK (ish) when working with PgJDBC directly, but it breaks code that expects this to work like it does in other databases where setString(...) can be used to assign to anything that's castable from varchar. Pg doesn't allow `unknown' to be passed as the type of a parameterised statement, so the JDBC driver can't work around this by passing such entries as fields of unknown type and letting the server work it out. It'd instead have to ask the server what are the permissible types for the placeholder $1 in this query ... which AFAIK isn't possible, and would require extra round trips too. I currently work around this by creating additional implicit casts where I need them, eg text-xml, text-json. It'd be lovely not to have to do that, though. -- Craig Ringer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] A note about add_path() and parameterized paths
I've been looking more closely at add_path() and related functions while trying to decide exactly how I want to handle cases where all the available paths for a relation are parameterized (because it's got unresolved lateral references). It suddenly struck me that I missed a bet while revising that logic for 9.2. I hacked add_path() so that it would ignore pathkeys for parameterized paths, essentially creating a policy that a parameterized path can't win on sort order. (Which is reasonable since it's going to end up on the inside of a nestloop, where sort ordering won't help anything.) However, it's still giving credit to parameterized paths if they have a cheap startup cost --- and that's equally uninteresting for anything on the inside of a nestloop. What we should do is tweak add_path() so that it also discounts startup cost as a figure of merit for parameterized paths. (This seems to be reasonably easy to do by making compare_path_costs_fuzzily not allow a parameterized path to win on the basis of startup cost. We should also teach add_path_precheck about that.) This gives us two benefits: 1. Slightly fewer parameterized paths survive the add_path tournament. Since the others were losers anyway, this saves cycles with no downside. 2. AFAICS, the re-comparisons being done in add_parameterized_path() become entirely unnecessary. Any parameterized path that has survived until we reach set_cheapest must win on total cost or rowcount compared to others of its parameterization. All such paths seem worthy to be considered in the parts of joinpath.c that accept parameterized input paths. So we can replace that whole step by just building a list of surviving parameterized paths and sticking it into rel-cheapest_parameterized_paths. (I also thought for a bit about dispensing with that list completely, and just scanning rel-pathlist looking for parameterized paths. But experimentation suggests there are often dozens of surviving non-parameterized paths and few if any parameterized ones, so scanning the whole pathlist to find the latter seems like a loser.) I shall manfully resist the temptation to retrofit this idea into 9.2, but I'm going to go do it in HEAD ... 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] FATAL: bogus data in lock file postmaster.pid:
On Tue, Aug 28, 2012 at 04:25:36PM -0400, Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
Updated patch attached which just reports the file as empty. I assume
we don't want the extra text output for pg_ctl like we do for the
backend.
The backend side of this looks mostly sane to me (but drop the \n,
messages are not supposed to contain those). But the feof test proposed
Removed. I thought we needed to add \n so that strings 80 would wrap
properly. How do we handle this?
for pg_ctl is no good: consider a file containing just, say, -.
fscanf would eat the -, then hit eof, and this would complain the file
is empty. Possibly checking for ftell(pidf) == 0 would do, though I'm
not sure whether it's portable to assume fscanf would eat a non-numeric
character before complaining.
ftell() seems to work fine when combined with feof(), so I used that in
the attached patch. ftell() alone remains at zero if the file contains
A, so feof() is also needed.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
new file mode 100644
index 775d71f..eadfcbf
*** a/src/backend/utils/init/miscinit.c
--- b/src/backend/utils/init/miscinit.c
*** CreateLockFile(const char *filename, boo
*** 766,771
--- 766,781
filename)));
close(fd);
+ if (len == 0)
+ {
+ ereport(FATAL,
+ (errcode(ERRCODE_LOCK_FILE_EXISTS),
+ errmsg(lock file \%s\ is empty, filename),
+ errhint(
+ Either another server is starting, or the lock file is the remnant
+ of a previous server startup crash.)));
+ }
+
buffer[len] = '\0';
encoded_pid = atoi(buffer);
diff --git a/src/bin/pg_ctl/pg_ctl.c b/src/bin/pg_ctl/pg_ctl.c
new file mode 100644
index af8d8b2..81ba39e
*** a/src/bin/pg_ctl/pg_ctl.c
--- b/src/bin/pg_ctl/pg_ctl.c
*** get_pgpid(void)
*** 292,299
}
if (fscanf(pidf, %ld, pid) != 1)
{
! write_stderr(_(%s: invalid data in PID file \%s\\n),
! progname, pid_file);
exit(1);
}
fclose(pidf);
--- 292,304
}
if (fscanf(pidf, %ld, pid) != 1)
{
! /* Is the file empty? */
! if (ftell(pidf) == 0 feof(pidf))
! write_stderr(_(%s: the PID file \%s\ is empty\n),
! progname, pid_file);
! else
! write_stderr(_(%s: invalid data in PID file \%s\\n),
! progname, pid_file);
exit(1);
}
fclose(pidf);
--
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] FATAL: bogus data in lock file postmaster.pid:
Bruce Momjian br...@momjian.us writes: On Tue, Aug 28, 2012 at 04:25:36PM -0400, Tom Lane wrote: The backend side of this looks mostly sane to me (but drop the \n, messages are not supposed to contain those). But the feof test proposed Removed. I thought we needed to add \n so that strings 80 would wrap properly. How do we handle this? We don't. Per the message style guidelines, it's the responsibility of a client frontend to line-wrap such text if it feels the need to. The backend has no business assuming that 80 characters (or any other number) is where to wrap. 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] default deny for roles
Andrew Dunstan and...@dunslane.net writes: On 08/28/2012 09:09 PM, Craig Ringer wrote: Wouldn't that render the user utterly unable to do anything until you added a bunch of GRANTs on the system catalogs for that user or a group they're a member of? Try it and see. You can do a lot without having any access rights at all to the catalog tables. Craig's got a really good point though: if we had the ability to revoke public, it would mean that something as simple as SELECT 2+2 would stop working. Or at least it ought to, since execute permissions on int4pl() are granted to PUBLIC, and the proposal is for the role to not have such permissions. While you can in fact do a lot without any explicit catalog access, I doubt that anyone will get far without the ability to use +, =, count(), etc. So that sounds like a killer argument from here. The only way you would end up with a usable database is if you somehow said well, I didn't really mean that for built-in objects ... but at that point I think you have to stop asking to change the behavior of the PUBLIC role. Instead make your own user-defined role that includes all your users except for the locked-down roles, and grant permissions on your non-system objects to that role not PUBLIC. 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] 64-bit API for large object
Tatsuo Ishii is...@postgresql.org writes: Correct me if I am wrong. After expanding large object API to 64-bit, the max size of a large object will be 8TB(assuming 8KB default BLKSZ). large object max size = pageno(int32) * LOBLKSIZE = (2^32-1) * (BLCKSZ / 4) = (2^32-1) * (8192/4) = 8TB I just want to confirm my calculation is correct. pg_largeobject.pageno is a signed int, so I don't think we can let it go past 2^31-1, so half that. We could buy back the other bit if we redefined the column as oid instead of int4 (to make it unsigned), but I think that would create fairly considerable risk of confusion between the loid and pageno columns (loid already being oid). I'd just as soon not go there, at least not till we start seeing actual field complaints about 4TB being paltry ;-) 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] 64-bit API for large object
pg_largeobject.pageno is a signed int, so I don't think we can let it go past 2^31-1, so half that. We could buy back the other bit if we redefined the column as oid instead of int4 (to make it unsigned), but I think that would create fairly considerable risk of confusion between the loid and pageno columns (loid already being oid). I'd just as soon not go there, at least not till we start seeing actual field complaints about 4TB being paltry ;-) Agreed. 4TB should be enough. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] MySQL search query is not executing in Postgres DB
On Tue, Aug 28, 2012 at 4:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, I think that when there is only one LPAD function, there is also very little chance that the results will come out differently than the user expected. [ shrug... ] I'm having a hard time resisting the temptation to point out that there are two. Fine, but as they have different numbers of arguments it has no bearing on the point at hand, which is that right now it is very easy to write a call that matches unexpectedly fails to match either one. The real point here though is that the proposed behavior change will affect all functions, not only the cases where you think there is only one sane behavior. And features such as search paths and default parameters frequently mean that there are more potential matches than the user thought of while writing the query. I'm totally unpersuaded by this argument. I have yet to run into a customer who defined multiple functions with the same name and then complained because we called the wrong one, or even because we threw an error instead of just picking one. I have run into MANY customers who have been forced to insert typecasts into applications to work around our willingness to consider calling the only plausible candidate function or operator. Now some of this is no doubt because we have very few customers running on pre-8.3 releases (woohoo!), but that's exactly the point: the bad old days when you could break your application by accidentally invoking the wrong function are gone. That problem is dead. What we ought to be focusing on now is fixing the collateral damage. In the end, SQL is a fairly strongly typed language, especially in our manifestation of it. I don't think we should give that up, especially not for benefits as dubious as not having to write a cast to make it clear that yes you really do want a timestamp to be treated as text. IMO, saving people from the errors that inevitably arise from that sort of sloppy thinking is a benefit, not a cost, of having a typed language. The benefit is that it allows us to be compatible with other SQL systems. If PostgreSQL were the only database in the world, I might agree with you, but it isn't: it's just the only one that requires you to insert this many casts. It's hard to accept the argument that there's no sensible alternative when other people have clearly found something that works for them and their users. We can dig in our heels and insist we know better, but what does that do other than drive away users? For most people, the database is just a tool, and they want it to work with a minimum of fuss, not force them to jump through unexpected and unwelcome hoops. Again, if there's real ambiguity then that is one thing, but what I'm proposing does not change the behavior in any case we currently consider ambiguous. I don't know of any other programming language or system where it is considered a virtue to force the user to inject unnecessary decorations into their code. Indeed, many systems go to quite some lengths to minimize the amount of such decoration that is required. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Incorrect behaviour when using a GiST index on points
On Tue, Aug 28, 2012 at 5:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Aug 27, 2012 at 7:43 PM, Tom Lane t...@sss.pgh.pa.us wrote: There's also the big-picture question of whether we should just get rid of fuzzy comparisons in the geometric types instead of trying to hack indexes to work around them. +1 for that approach, but only if I don't have to do the work. Otherwise, +1 for doing the simplest thing that we're sure will eliminate wrong answers. What we're forced to speculate about here is how many applications out there are relying on fuzzy comparison to get answers they like, versus how many are getting answers they don't like because of it. The fact that the underlying storage is float8 not numeric suggests there are probably some cases where fuzzy is helpful. I figured it mostly ended up that way because most of the geometic datatypes are built on top of float8s, and most of the GiST distance metrics are therefore a float8 distance. But I must be confused, because surely we don't need to remove the option to express the penalty as a float8, only the prohibition on using anything else. In which case this next part seems like a non-issue: Another issue here is that even if we agree that simple comparisons (operator complexity up to about the limit of what an index might support) should be exact, there's something to be said for fuzzy computations for operators like whether a point falls on a line. Internal roundoff error makes that problematic even if you assume that the inputs are exact. I've never cared for the particulars of the way the fuzzy comparisons are done, in any case: using an absolute rather than relative error threshold is wrong according to every numerical analysis principle I know. Yeah, that seemed odd to me, too. The long and the short of it is that it will probably take a significant investment of work to make something that's clearly better. If that weren't the case, we'd have done something long ago. Perhaps, but this patch has been kicking around for 7 months without any on-list review, so there might also be a lack of interest in fixing the problem. :-( -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] 64-bit API for large object
On Tue, Aug 28, 2012 at 10:51 PM, Tatsuo Ishii is...@postgresql.org wrote: pg_largeobject.pageno is a signed int, so I don't think we can let it go past 2^31-1, so half that. We could buy back the other bit if we redefined the column as oid instead of int4 (to make it unsigned), but I think that would create fairly considerable risk of confusion between the loid and pageno columns (loid already being oid). I'd just as soon not go there, at least not till we start seeing actual field complaints about 4TB being paltry ;-) Agreed. 4TB should be enough. ...for anybody! -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] [BUGS] BUG #6572: The example of SPI_execute is bogus
On Sun, Apr 15, 2012 at 12:29:39PM -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Apr 5, 2012 at 2:39 AM, Hitoshi Harada umi.tan...@gmail.com wrote: On Wed, Apr 4, 2012 at 8:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: Given the lack of complaints since 9.0, maybe we should not fix this but just redefine the new behavior as being correct? But it seems mighty inconsistent that the tuple limit would apply if you have RETURNING but not when you don't. In any case, the ramifications are wider than one example in the SPI docs. To be honest, I was surprised when I found tcount parameter is said to be applied to even INSERT. I believe people think that parameter is to limit memory consumption when returning tuples thus it'd be applied for only SELECT or DML with RETURNING. So I'm +1 for non-fix but redefine the behavior. Who wants to limit the number of rows processed inside the backend, from SPI? Yeah. Okay, apparently nobody cares about RETURNING behaving differently from non-RETURNING, so the consensus is to redefine the current behavior as correct. That means what we need is to go through the docs and see what places need to be updated (and, I guess, back-patch the changes to 9.0). I will get to this if nobody else does, but not right away. Would someone make the doc change outlined above? Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] default deny for roles
On 08/28/2012 10:42 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 08/28/2012 09:09 PM, Craig Ringer wrote: Wouldn't that render the user utterly unable to do anything until you added a bunch of GRANTs on the system catalogs for that user or a group they're a member of? Try it and see. You can do a lot without having any access rights at all to the catalog tables. Craig's got a really good point though: if we had the ability to revoke public, it would mean that something as simple as SELECT 2+2 would stop working. Or at least it ought to, since execute permissions on int4pl() are granted to PUBLIC, and the proposal is for the role to not have such permissions. While you can in fact do a lot without any explicit catalog access, I doubt that anyone will get far without the ability to use +, =, count(), etc. So that sounds like a killer argument from here. The only way you would end up with a usable database is if you somehow said well, I didn't really mean that for built-in objects ... but at that point I think you have to stop asking to change the behavior of the PUBLIC role. Instead make your own user-defined role that includes all your users except for the locked-down roles, and grant permissions on your non-system objects to that role not PUBLIC. Yeah, what I've done in the past is revoke public privs from the catalog tables and the information schema, and granted them to a pseudo-public role. This has left intact the public privs of things like int4pl(). This works quite well for hiding schema details from a non-member of the pseudo-public role, which was the aim. But if you want a user truly only able to use some specified functions, say, maybe you would revoke the lot. That's a fairly paranoid security model, but not beyond imagining. (None of this is to say I think David's suggestion is a good one.) 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] MySQL search query is not executing in Postgres DB
Robert Haas robertmh...@gmail.com writes: On Tue, Aug 28, 2012 at 4:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: The real point here though is that the proposed behavior change will affect all functions, not only the cases where you think there is only one sane behavior. And features such as search paths and default parameters frequently mean that there are more potential matches than the user thought of while writing the query. I'm totally unpersuaded by this argument. I have yet to run into a customer who defined multiple functions with the same name and then complained because we called the wrong one, or even because we threw an error instead of just picking one. That argument would hold water if we got rid of every single usage of overloading in the system-defined operators/functions, which as you well know is not an attractive idea. Since that's not going to happen, arguing for this on the basis that your customers don't overload function names is missing the point. Any loosening of the rules is going to create issues for system-function resolution ... unless you're going to propose that we somehow do this differently for user and system defined functions. I have run into MANY customers who have been forced to insert typecasts into applications to work around our willingness to consider calling the only plausible candidate function or operator. Now some of this is no doubt because we have very few customers running on pre-8.3 releases (woohoo!), but that's exactly the point: the bad old days when you could break your application by accidentally invoking the wrong function are gone. That problem is dead. The reason it's dead is that we killed it in 8.3. I don't want it coming back to life, but I think that that will be exactly the outcome if we let any implicit casts to text get back into the rules for operator/function overloading resolution. An example of the sort of problem that I don't want to hear about ever again is somebody trying to use max() on a point column. We don't have linear sort ordering for points, so this is nonsensical and should draw an error. Which it does, today. With your proposal, the system would silently use max(pointcol::text), producing results that might even look plausible if the user wasn't paying too much attention. If that's the behavior the user actually wants, fine: let him say so with an explicit cast to text. But I don't want the system trapping users into such hard-to-find errors because we are so focused on mysql compatibility that we let people omit conceptually-critical casts in the name of ease of use. For most people, the database is just a tool, and they want it to work with a minimum of fuss, not force them to jump through unexpected and unwelcome hoops. Again, if there's real ambiguity then that is one thing, but what I'm proposing does not change the behavior in any case we currently consider ambiguous. I don't know of any other programming language or system where it is considered a virtue to force the user to inject unnecessary decorations into their code. Really? You've not had experience with very many programming languages, then. Just about every one I've ever dealt with that's at a higher conceptual level than C or BASIC *is* sticky about this sort of thing. 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] Audit Logs WAS: temporal support patch
2012/8/28 Jim Nasby j...@nasby.net: On 8/28/12 2:51 PM, Pavel Stehule wrote: The thing I don't like about this is it assumes that time is the best way to refer to when things changed in a system. Not only is that a bad assumption, it also means that relating things to history becomes messy. On second hand I don't have a problem with some optional counter, although I think so database system time is very useful and other counters for versioning are not necessary - because in one time I can have only one version - it doesn't do versions from rollbacked transactions. What happens if the system clock runs backwards? probably, than you have more significant issues than this - it can be same with overloading any counter What happens if two transactions start in the same microsecond? (And I know for a fact that's possible, because I've seen it). yes, it is possible - and probably you need to know end of transaction - commit time - auxilary counter doesn't help - because it can be in different order too - when first transacttion was rollbacked Pavel More importantly, I believe using time to handle recording a versioned history of something is flawed to begin with. You might care about what time a new version was created; but what's far more important is recording the correct ordering of things, and time isn't actually a great way to do that. Note that no version control systems use time as their primary attribute. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] splitting htup.h
... btw, the buildfarm says you forgot contrib/ while fixing the collateral damage from these changes. 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] FATAL: bogus data in lock file postmaster.pid:
Excerpts from Bruce Momjian's message of mar ago 28 22:21:27 -0400 2012: On Tue, Aug 28, 2012 at 04:25:36PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Updated patch attached which just reports the file as empty. I assume we don't want the extra text output for pg_ctl like we do for the backend. The backend side of this looks mostly sane to me (but drop the \n, messages are not supposed to contain those). But the feof test proposed Removed. It's a pretty strange line wrap you got in this version of the patch. Normally we just let the string run past the 78 char limit, without cutting it in any way. And moving the start of the string to the line following errhint( looks very odd to me. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training 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] MySQL search query is not executing in Postgres DB
I wrote:
Robert Haas robertmh...@gmail.com writes:
That problem is dead.
The reason it's dead is that we killed it in 8.3. I don't want it
coming back to life, but I think that that will be exactly the outcome
if we let any implicit casts to text get back into the rules for
operator/function overloading resolution.
To put some concreteness into what so far has been a pretty hand-wavy
discussion, I experimented with the attached patch. I'm not sure that
it exactly corresponds to what you proposed, but I think this is the
only place the consideration could be injected without a substantial
amount of code rearrangement. This results in half a dozen regression
test failures (see second attachment), which mostly consist of
function/operator does not exist errors changing to function/operator
is not unique. I've not looked into exactly why each one happens ---
possibly the code is now finding multiple string-category matches where
before it found none. But it definitely illustrates my point that this
would not be without surprises.
Oh, one more thing:
regression=# select lpad(42,8);
ERROR: failed to find conversion function from integer to text
so this doesn't actually solve the problem you want to solve.
I'm not sure why that's happening, either, but evidently some
additional coercion laxity would required.
regards, tom lane
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 2b1a13a..feac9f9 100644
*** a/src/backend/parser/parse_func.c
--- b/src/backend/parser/parse_func.c
*** func_match_argtypes(int nargs,
*** 555,560
--- 555,578
}
}
+ if (ncandidates == 0)
+ {
+ /* try again with assignment rules */
+ for (current_candidate = raw_candidates;
+ current_candidate != NULL;
+ current_candidate = next_candidate)
+ {
+ next_candidate = current_candidate-next;
+ if (can_coerce_type(nargs, input_typeids, current_candidate-args,
+ COERCION_ASSIGNMENT))
+ {
+ current_candidate-next = *candidates;
+ *candidates = current_candidate;
+ ncandidates++;
+ }
+ }
+ }
+
return ncandidates;
} /* func_match_argtypes() */
*** /home/tgl/pgsql/src/test/regress/expected/text.out Tue Jul 12 18:56:58 2011
--- /home/tgl/pgsql/src/test/regress/results/text.out Wed Aug 29 00:08:45 2012
***
*** 26,35
-- As of 8.3 we have removed most implicit casts to text, so that for example
-- this no longer works:
select length(42);
! ERROR: function length(integer) does not exist
LINE 1: select length(42);
^
! HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
-- But as a special exception for usability's sake, we still allow implicit
-- casting to text in concatenations, so long as the other input is text or
-- an unknown literal. So these work:
--- 26,35
-- As of 8.3 we have removed most implicit casts to text, so that for example
-- this no longer works:
select length(42);
! ERROR: function length(integer) is not unique
LINE 1: select length(42);
^
! HINT: Could not choose a best candidate function. You might need to add
explicit type casts.
-- But as a special exception for usability's sake, we still allow implicit
-- casting to text in concatenations, so long as the other input is text or
-- an unknown literal. So these work:
***
*** 47,56
-- but not this:
select 3 || 4.0;
! ERROR: operator does not exist: integer || numeric
LINE 1: select 3 || 4.0;
^
! HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
/*
* various string functions
*/
--- 47,56
-- but not this:
select 3 || 4.0;
! ERROR: operator is not unique: integer || numeric
LINE 1: select 3 || 4.0;
^
! HINT: Could not choose a best candidate operator. You might need to add
explicit type casts.
/*
* various string functions
*/
==
*** /home/tgl/pgsql/src/test/regress/expected/errors.outThu Jan 26
17:29:22 2012
--- /home/tgl/pgsql/src/test/regress/results/errors.out Wed Aug 29 00:08:52 2012
***
*** 126,132
stype = int4,
finalfunc = int2um,
initcond = '0');
! ERROR: function int2um(integer) does not exist
-- left out basetype
create aggregate newcnt1 (sfunc = int4inc,
stype = int4,
--- 126,132
stype = int4,
finalfunc = int2um,
initcond = '0');
! ERROR: function int2um(smallint) requires run-time type coercion
-- left out basetype
create aggregate newcnt1 (sfunc = int4inc,
stype = int4,
Re: [HACKERS] FATAL: bogus data in lock file postmaster.pid:
On Wed, Aug 29, 2012 at 12:24:26AM -0400, Alvaro Herrera wrote:
Excerpts from Bruce Momjian's message of mar ago 28 22:21:27 -0400 2012:
On Tue, Aug 28, 2012 at 04:25:36PM -0400, Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
Updated patch attached which just reports the file as empty. I assume
we don't want the extra text output for pg_ctl like we do for the
backend.
The backend side of this looks mostly sane to me (but drop the \n,
messages are not supposed to contain those). But the feof test proposed
Removed.
It's a pretty strange line wrap you got in this version of the patch.
Normally we just let the string run past the 78 char limit, without
cutting it in any way. And moving the start of the string to the line
following errhint( looks very odd to me.
OK, updated patch attached.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
new file mode 100644
index 775d71f..efd5152
*** a/src/backend/utils/init/miscinit.c
--- b/src/backend/utils/init/miscinit.c
*** CreateLockFile(const char *filename, boo
*** 766,771
--- 766,780
filename)));
close(fd);
+ if (len == 0)
+ {
+ ereport(FATAL,
+ (errcode(ERRCODE_LOCK_FILE_EXISTS),
+ errmsg(lock file \%s\ is empty, filename),
+ errhint(
+ Either another server is starting, or the lock file is the remnant of a previous server startup crash.)));
+ }
+
buffer[len] = '\0';
encoded_pid = atoi(buffer);
diff --git a/src/bin/pg_ctl/pg_ctl.c b/src/bin/pg_ctl/pg_ctl.c
new file mode 100644
index af8d8b2..81ba39e
*** a/src/bin/pg_ctl/pg_ctl.c
--- b/src/bin/pg_ctl/pg_ctl.c
*** get_pgpid(void)
*** 292,299
}
if (fscanf(pidf, %ld, pid) != 1)
{
! write_stderr(_(%s: invalid data in PID file \%s\\n),
! progname, pid_file);
exit(1);
}
fclose(pidf);
--- 292,304
}
if (fscanf(pidf, %ld, pid) != 1)
{
! /* Is the file empty? */
! if (ftell(pidf) == 0 feof(pidf))
! write_stderr(_(%s: the PID file \%s\ is empty\n),
! progname, pid_file);
! else
! write_stderr(_(%s: invalid data in PID file \%s\\n),
! progname, pid_file);
exit(1);
}
fclose(pidf);
--
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] [WIP] Performance Improvement by reducing WAL for Update Operation
On August 27, 2012 7:00 PM Amit Kapila wrote: On August 27, 2012 5:58 PM Heikki Linnakangas wrote: On 27.08.2012 15:18, Amit kapila wrote: I have implemented the WAL Reduction Patch for the case of HOT Update as Let's do it for HOT updates only. Simon Robert made good arguments on why this is a bad idea for non-HOT updates. Okay, I shall do it that way. So now I shall send information about all the testing I have done for this Patch and then Upload it in CF. Test Scenario's are below and testcases for same are attached with this mail. Scenario1: Recover the data where the field data is updated with different value from an exisitng data of an integer field. Steps: 1. Start the server, create table, insert one record into the table. 2. update the integer field with other than existing data. 3. Shutdown the server immediately. 4. Start the server and connect the client and check the data in the table. Expected behavior: The updated data should present in the table after database recovery. Scenario2: Recover the data where the field data is updated with different value from an exisitng data of char and varchar fields. Steps: 1. Start the server, create table, insert one record into the table. 2. update both char and varchar fields with other than existing data. 3. Shutdown the server immediately. 4. Start the server and connect the client and check the data in the table. Expected behavior: The updated data should present in the table after database recovery. Scenario3: Recover the data where the field data is updated with NULL value from an exisitng data of a field. Steps: 1. Start the server, create table, insert one record into the table. 2. update a field with NULL value. 3. Shutdown the server immediately. 4. Start the server and connect the client and check the data in the table. Expected behavior: The updated data should present in the table after database recovery. Scenario4: Recover the data where the field data is updated with a proper value from an exisitng data of a field where the row contains NULL data. Steps: 1. Start the server, create table, insert one record into the table. 2. update a field with a different value other than existing data. 3. Shutdown the server immediately. 4. Start the server and connect the client and check the data in the table. Expected behavior: The updated data should present in the table after database recovery. Scenario5: Recover the data where all fields data is updated with NULL value from an exisitng data of a fields. Steps: 1. Start the server, create table, insert one record into the table. 2. update all fields with NULL values. 3. Shutdown the server immediately. 4. Start the server and connect the client and check the data in the table. Expected behavior: The updated data should present in the table after database recovery. Scenario6: Recover the data of updated field of a table where the table contains a toast table. Steps: 1. Start the server, create table, insert one record into the table. 2. update a field with a different value other than existing data. 3. Shutdown the server immediately. 4. Start the server and connect the client and check the data in the table. Expected behavior: The updated data should present in the table after database recovery. Scenario7: Recover the data of updated field of a table where the row length is less than 128 bytes. Steps: 1. Start the server, create table, insert one record into the table. 2. update a field with a different value other than existing data. 3. Shutdown the server immediately. 4. Start the server and connect the client and check the data in the table. Expected behavior: The updated data should present in the table after database recovery. Scenario8: Recover the data of updated field of a table where the before trigger modifies the tuple before the tuple updates. Steps: 1. Start the server, create table, insert one record into the table. 2. create a before trigger which modifies the same record. 3. update a field with a different value other than existing data. 4. Shutdown the server immediately. 5. Start the server and connect the client and check the data in the table. Expected behavior: The updated data should present in the table after database recovery. Scenario9: Recover the data where the update operation fails because of trigger returns NULL. Steps: 1. Start the server, create table, insert one record into the table. 2. update a field fails as before trigger returns NULL. 3. Shutdown the server immediately. 4. Start the server and connect the client and check the data in the table. Expected behavior: The update command shouldn't be effective after recovery also. With Regards, Amit Kapila.-- Test case 1 drop table if exists tbl; create table tbl(f1 int, f2 varchar(100), f3 float8, f4 char(200)); insert into tbl values(1,'hari',2.1,'test'); checkpoint; -- first update is as it creates a
Re: [HACKERS] FATAL: bogus data in lock file postmaster.pid:
Bruce Momjian br...@momjian.us writes: On Wed, Aug 29, 2012 at 12:24:26AM -0400, Alvaro Herrera wrote: It's a pretty strange line wrap you got in this version of the patch. Normally we just let the string run past the 78 char limit, without cutting it in any way. And moving the start of the string to the line following errhint( looks very odd to me. OK, updated patch attached. I agree with Alvaro's complaint that moving the whole string literal to the next line isn't conformant to our usual coding style. Definitely nitpicky, but why would you do it like that? 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
