Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
On 02/02/2011 08:22 PM, Dimitri Fontaine wrote:
Either one line in the Makefile or a new file with the \i equivalent
lines, that would maybe look like:
SELECT pg_execute_sql_file('upgrade.v14.sql');
SELECT pg_execute_sql_file('upgrade.v15.sql');
So well… I don't see how you've made it less gross here.
Chaining the upgrade files should be relatively easy, if something like
pg_execute_sql_file would be available (actually it would need to be
pg_execute_extension_file so that @extschema@ would be substituted
correctly).
Example:
upgrade_from_1_0 = '1.0 = upgrade_from_1.0.sql'
upgrade_from_2_0 = '2.0 = upgrade_from_2.0.sql'
upgrade_from_3_0 = '3.0 = upgrade_from_3.0.sql'
upgrade_from_1.0.sql contents:
alter table foobar add column id2 integer;
pg_execute_extension_file('upgrade_from_2.0.sql');
upgrade_from_2.0.sql contents:
alter table foobar add column id3 integer;
pg_execute_extension_file('upgrade_from_3.0.sql');
...
So, when creating a new version you would need to update the main .sql
file, create a new upgrade file, and alter the
upgrade_from_previous_version.sql to include the new upgrade file. This
should be relatively easy to maintain. Also, this would give you the
freedom to not chain the files when that is not appropriate.
By the way, I saw that the character '.' is not allowed in the xxx part
of upgrade_from_xxx and this is not documented in the patch. What can be
in the xxx part, and is this documented somewhere else?
- Anssi
--
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] ALTER EXTENSION UPGRADE, v3
On 02/03/2011 12:23 AM, Robert Haas wrote:
[..]
-- unconditional stuff
[..6]
-- stuff to do if coming from pre-7
[..]
-- some more unconditional stuff
[6..12]
-- stuff to do if coming from between 6 and 12
[..]
-- a few more unconditional things
This might be a stupid idea, but how about introducing another
placeholder variable in addition to @extschema@ when upgrading, named
maybe @fromversion@. Combined with do blocks and
pg_execute_extension_file this would allow doing the above stuff:
upgrade.sql contents:
[..]
-- uncoditional stuff
[..6]
DO $$
begin
if @fromversion@ matches [..6] then
pg_execute_extension_file('stuff to do if coming from pre-7-file');
end if;
end;
$$
language 'plpgsql';
...
- Anssi
--
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] compiler warning
On Thu, Feb 3, 2011 at 04:40, Bruce Momjian br...@momjian.us wrote: I am seeing the following compiler warning for the past few days: basebackup.c:213: warning: variable `ptr' might be clobbered by `longjmp' or `vfork' and I see this comment in the file: /* * Actually do a base backup for the specified tablespaces. * * This is split out mainly to avoid complaints about variable might be * clobbered by longjmp from stupider versions of gcc. */ Seems that isn't working as expected. I am using: gcc version 2.95.3 20010315 (release) with -O1. This is the same warning Tom fixed earlier. I have no idea what actually causes it :( I think it's somehow caused by the PG_ENSURE_ERROR_CLEANUP stuff. Does it go away if you break everything inside the if (opt-includewal) into it's own function? (Or at least everything except the pq_putemptymessage() call, because moving that would make the code very confusing) -- 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] ALTER EXTENSION UPGRADE, v3
Robert Haas robertmh...@gmail.com writes: Stepping back from the implementation details and file naming conventions a bit, it seems to me that when you do schema upgrades, there are basically three possible things you might want to put in the upgrade script: [...] I've managed schema upgrades that went through dozens of versions, and making sure that you can correctly upgrade from every previous version to v48 is definitely going to be a challenge. David's approach makes that a little simpler in some ways, but I think it falls down pretty badly on point #3. Where I agree with you is that we're talking about a complex problem, and whatever tools we have to handle it will not make it any simpler. The only thing that maybe could be made simpler is how to write the solution, not how to design what it is. I'd actually be inclined to just have ONE upgrade file and invent some kind of meta-language for controlling which statements get executed. I'm currently unimpressed by the idea of inventing new tools to solve a fairly common problem. All the more when this syntax is based on SQL when the problem space is all about composing script files. I see your proposal is not so much SQL'ish, though. All in all, I think we should postpone solving this problem to until we have covered the basis, that is running 1 given determined script to handle 1 specific upgrade that the DBA is driving. I think my proposal covers that and avoids painting us into a corner should we ever decide to walk the extra mile here. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
Anssi Kääriäinen anssi.kaariai...@thl.fi writes:
upgrade_from_2.0.sql contents:
alter table foobar add column id3 integer;
pg_execute_extension_file('upgrade_from_3.0.sql');
...
So, when creating a new version you would need to update the main .sql file,
create a new upgrade file, and alter the upgrade_from_previous_version.sql
to include the new upgrade file. This should be relatively easy to
maintain. Also, this would give you the freedom to not chain the files when
that is not appropriate.
Again, why not, I think I can see how this would work out. What I don't
see is what is the gain compared to preparing the right files at make
time and only shipping autonomous files. I very much prefer to handle
a set of source SQL files and some cat a b c ship.sql rules rather
than ship loads of files that all depends on each other.
By the way, I saw that the character '.' is not allowed in the xxx part of
upgrade_from_xxx and this is not documented in the patch. What can be in the
xxx part, and is this documented somewhere else?
It has to be a valid configuration variable name, as any other GUC, and
it's not a placeholder (only those can contain dots). We're using the
same parser as for postgresql.conf and recovery.conf here. Not sure
where that is documented, though.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
This might be a silly idea, but I'm not sure if it was really
discussed with respect to extensions here before...
Why not use PL/pgSQL as install/upgrade script language,
specially now when it's included in the core by default?
This would allow for relatively straightforward inclusion
of conditional logic, etc in install/upgrade scripts.
All current contrib stuff runs great as PL/pgSQL, for example.
The @ placeholder syntax just seems outright hideous and doesn't
feel quite right either.
Also worth considering might be, how the proposed extension
system relates to SQL standard modules (ie. CREATE MODULE, etc),
which might be implemented in the future.
There are are some obvious overlaps (relocatable/fixed extensions vs.
modules with/without schemas, for example).
Also, consider the following case:
I have two different users, u1 and u2 with corresponding schemas, u1 and u2,
both using hstore, for example. Right now, I can load hstore.sql contrib
into both, u1 and u2 it all works great.
If I understand correctly, such an arrangement would not really be supported
with extensions as they stand now.
Ziga Kranjec,
developer, ljudmila.org
On Feb 3, 2011, at 9:28 AM, Anssi Kääriäinen wrote:
On 02/03/2011 12:23 AM, Robert Haas wrote:
[..]
-- unconditional stuff
[..6]
-- stuff to do if coming from pre-7
[..]
-- some more unconditional stuff
[6..12]
-- stuff to do if coming from between 6 and 12
[..]
-- a few more unconditional things
This might be a stupid idea, but how about introducing another placeholder
variable in addition to @extschema@ when upgrading, named maybe
@fromversion@. Combined with do blocks and pg_execute_extension_file this
would allow doing the above stuff:
upgrade.sql contents:
[..]
-- uncoditional stuff
[..6]
DO $$
begin
if @fromversion@ matches [..6] then
pg_execute_extension_file('stuff to do if coming from pre-7-file');
end if;
end;
$$
language 'plpgsql';
...
- Anssi
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Typo in create user mapping docs page
Someone submitted a comment to the docs (which I'll shortly delete) which points out a typo on the CREATE USER MAPPING page in the docs. A very brief patch is attached. They also added: Also concerning this paragraph: a little more detail on how exactly the two sources of encapsuled information are combined would be helpful. Thanks -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 create_user_mapping_typo.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Move WAL warning
On Wed, Feb 2, 2011 at 18:00, Magnus Hagander mag...@hagander.net wrote:
On Wed, Feb 2, 2011 at 17:43, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
On 02.02.2011 16:36, Magnus Hagander wrote:
When running pg_basebackup with -x to include all transaction log, the
server will still throw a warning about xlog archiving if it's not
enabled - that is completely irrelevant since pg_basebackup has
included it already (and if it was gone, the base backup step itself
will fail - actual error and not warning).
This patch moves the warning from do_pg_base_backup to pg_base_backup,
so it still shows when using the explicit function calls, but goes
away when using pg_basebackup.
For the sake of consistency, how about moving the pg_stop_backup complete,
all required WAL segments have been archived notice too?
Well, it goes out as a NOTICE, so by default it doesn't show.. But
yeah, for code-consistency it makes sense. Like so, then.
Thinking some more about it, I realized this is not going to be enough
- we need to be able to turn off the waiting for WAL segment as well,
in the case when you're streaming the log. Thus, it needs to be
controllable from the backup client, and we can't just assume the
default is ok.
Attached is an updated patch that adds a NOWAIT option to BASE_BACKUP,
that turns off the waiting. If it's set, it also doesn't warn about
not being able to wait in the case when there is nothing to wait for,
so this is a replacement for the previous patch.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 66cc004..a48e36c 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -8615,7 +8615,7 @@ pg_stop_backup(PG_FUNCTION_ARGS)
XLogRecPtr stoppoint;
char stopxlogstr[MAXFNAMELEN];
- stoppoint = do_pg_stop_backup(NULL);
+ stoppoint = do_pg_stop_backup(NULL, true);
snprintf(stopxlogstr, sizeof(stopxlogstr), %X/%X,
stoppoint.xlogid, stoppoint.xrecoff);
@@ -8630,7 +8630,7 @@ pg_stop_backup(PG_FUNCTION_ARGS)
* the non-exclusive backup specified by 'labelfile'.
*/
XLogRecPtr
-do_pg_stop_backup(char *labelfile)
+do_pg_stop_backup(char *labelfile, bool waitforarchive)
{
bool exclusive = (labelfile == NULL);
XLogRecPtr startpoint;
@@ -8829,7 +8829,7 @@ do_pg_stop_backup(char *labelfile)
* wish to wait, you can set statement_timeout. Also, some notices are
* issued to clue in anyone who might be doing this interactively.
*/
- if (XLogArchivingActive())
+ if (waitforarchive XLogArchivingActive())
{
XLByteToPrevSeg(stoppoint, _logId, _logSeg);
XLogFileName(lastxlogfilename, ThisTimeLineID, _logId, _logSeg);
@@ -8870,7 +8870,7 @@ do_pg_stop_backup(char *labelfile)
ereport(NOTICE,
(errmsg(pg_stop_backup complete, all required WAL segments have been archived)));
}
- else
+ else if (waitforarchive)
ereport(NOTICE,
(errmsg(WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup)));
diff --git a/src/backend/replication/basebackup.c b/src/backend/replication/basebackup.c
index 29284a6..0bd1fa0 100644
--- a/src/backend/replication/basebackup.c
+++ b/src/backend/replication/basebackup.c
@@ -37,6 +37,7 @@ typedef struct
const char *label;
bool progress;
bool fastcheckpoint;
+ bool nowait;
bool includewal;
} basebackup_options;
@@ -171,7 +172,7 @@ perform_base_backup(basebackup_options *opt, DIR *tblspcdir)
}
PG_END_ENSURE_ERROR_CLEANUP(base_backup_cleanup, (Datum) 0);
- endptr = do_pg_stop_backup(labelfile);
+ endptr = do_pg_stop_backup(labelfile, !opt-nowait);
if (opt-includewal)
{
@@ -251,6 +252,7 @@ parse_basebackup_options(List *options, basebackup_options *opt)
bool o_label = false;
bool o_progress = false;
bool o_fast = false;
+ bool o_nowait = false;
bool o_wal = false;
MemSet(opt, 0, sizeof(*opt));
@@ -285,6 +287,15 @@ parse_basebackup_options(List *options, basebackup_options *opt)
opt-fastcheckpoint = true;
o_fast = true;
}
+ else if (strcmp(defel-defname, nowait) == 0)
+ {
+ if (o_nowait)
+ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg(duplicate option \%s\, defel-defname)));
+ opt-nowait = true;
+ o_nowait = true;
+ }
else if (strcmp(defel-defname, wal) == 0)
{
if (o_wal)
diff --git a/src/backend/replication/repl_gram.y b/src/backend/replication/repl_gram.y
index e1a4a51..4930ad1 100644
--- a/src/backend/replication/repl_gram.y
+++ b/src/backend/replication/repl_gram.y
@@ -71,6 +71,7 @@ Node *replication_parse_result;
%token K_LABEL
%token K_PROGRESS
%token K_FAST
+%token K_NOWAIT
%token K_WAL
%token K_START_REPLICATION
@@ -107,7 +108,7 @@ identify_system:
;
/*
- * BASE_BACKUP [LABEL 'label'] [PROGRESS] [FAST] [WAL]
+ * BASE_BACKUP [LABEL 'label'] [PROGRESS] [FAST] [WAL]
Re: [HACKERS] [DOCS] Typo in create user mapping docs page
On Thu, Feb 3, 2011 at 11:07, Thom Brown t...@linux.com wrote: Someone submitted a comment to the docs (which I'll shortly delete) which points out a typo on the CREATE USER MAPPING page in the docs. A very brief patch is attached. Thanks, applied. -- 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] arrays as pl/perl input arguments [PATCH]
Hi,
On Feb 2, 2011, at 7:16 PM, Tim Bunce wrote:
I'm sorry I'm late to this party. I haven't been keeping up with
pgsql-hackers.
Better late than never :)
I'd kind'a hoped that this functionality could be tied into extending
PL/Perl to handle named arguments. That way the perl variables
corresponding to the named arguments could be given references without
breaking any code.
Franky I don't see a direct connection between conversion of arrays into array
references and supporting named arguments. Could you, please, show some
examples of how you hoped the functionality could be extended?
Some observations on the current code (based on a quick skim):
- I'd like to see the conversion function exposed as a builtin
$ref = decode_array_literal({...});
In principal, I think that's not hard to built with the functionality provided
by this patch. I see this as an XS function which takes the array string,
calls the array_in to convert it to the array datum, and, finally, calls
plperl_ref_from_pg_array (provided by the patch) to produce the resulting
array reference.
- Every existing plperl function that takes arrays is going to get
slower due to the overhead of parsing the string and allocating the
array and all its elements.
Well, per my understanding of Alex changes, the string parsing is not invoked
unless requested by referencing the array in a string context. Normally, onle
plperl_ref_from_pg_array will be invoked every time the function is called
with an array argument, which would take little time to convert the PostgreSQL
internal array representation (not a sting) to the perl references, but that's
no different from what is already done with composite type arguments, which
are converted to perl hash references on every corresponding function call.
- Some of those functions may not use the array at all and some may
simply pass it on as an argument to another function.
I don't see how it would be good to optimize for the functions that are
declared to get the array but in fact do nothing with it. And considering the
case of passing an array through to another function, it's currently not
possible to call another pl/perl function from an existing one directly, and
when passing muti-dimensional arrays to a perl function one would need to
convert it to the array references anyway.
- Making the conversion lazy would be a big help.
Converting it to string is already lazy, and, per the argumens above, I don't
see a real benefit of lazy conversion to the perl reference (as comparing to
the hurdles of implementing that).
/A
--
Alexey Klyukin
The PostgreSQL Company - Command Prompt, Inc.
--
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] LIKE, CHAR(), and trailing spaces
On Wed, Feb 02, 2011 at 07:48:38PM -0500, Bruce Momjian wrote: Brendan Jurd wrote: On 3 February 2011 10:54, Bruce Momjian br...@momjian.us wrote: It seems LIKE is considering the trailing CHAR(10) field spaces as significant, even though our documentations says: -- snip -- It says trailing spaces are not significant for character comparisons --- the real question is whether LIKE is a comparison. ?Obvioiusly '=' is a comparison, but the system does not treat LIKE as a comparison in terms of trailing spaces. ?Is that desired behavior? Interesting. I would have to say that from the user point of view, LIKE is definitely a comparison, and if the rest of the operators on bpchar ignore whitespace then LIKE ought to as well. Is the situation the same for regex matches (~ operators)? Yes, I think so: test= SELECT 'a'::char(10) ~ 'a$'; ?column? -- f (1 row) test= SELECT 'a'::char(10) ~ 'a *$'; ?column? -- t (1 row) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com In my mind LIKE/~ are pattern matching operators and not a simple comparison operator. PostgreSQL is doing the right thing in restricting the somewhat bizarre treatment of trailing spaces to the '=' comparison function. I can only imagine what would be needed to allow exceptions to the pattern matching syntax to allow you to actually work with and match the trailing spaces otherwise. +10 for leaving the behavior as is. Regards, Ken + 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 -- 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] ALTER EXTENSION UPGRADE, v3
On Thu, Feb 03, 2011 at 10:21:28AM +0200, Anssi Kääriäinen wrote:
On 02/02/2011 08:22 PM, Dimitri Fontaine wrote:
Either one line in the Makefile or a new file with the \i equivalent
lines, that would maybe look like:
SELECT pg_execute_sql_file('upgrade.v14.sql');
SELECT pg_execute_sql_file('upgrade.v15.sql');
So well… I don't see how you've made it less gross here.
Chaining the upgrade files should be relatively easy, if something like
pg_execute_sql_file would be available (actually it would need to be
pg_execute_extension_file so that @extschema@ would be substituted
correctly).
Example:
upgrade_from_1_0 = '1.0 = upgrade_from_1.0.sql'
upgrade_from_2_0 = '2.0 = upgrade_from_2.0.sql'
upgrade_from_3_0 = '3.0 = upgrade_from_3.0.sql'
Hmm, how about allowing a list of files to execute? That allows the
developer to create modularized sql, and composite it in the config:
for a mythical version 4.0:
1.0 = add_foobar_table.sql new_method_baz.sql
2.0 = drop_method_baz.sql add_quuz_table.sql
# oops, we still needed this
3.0 = new_method_baz.sql
I know when I'm developing such upgrades, the code looks like that,
until I need to shoehorn them into the upgrade systems idea of version
numbers matching names to find scripts to run.
The advantage of this is that it keeps the logic for mapping version
to upgrades in the config: the upgrade scripts mearly handle the actual
SQL for doing a specific task, not a collection of tasks only related by
virtue of being released at the same time.
Ross
--
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer Admin, Research Scientistphone: 713-348-6166
Connexions http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
--
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] ALTER EXTENSION UPGRADE, v3
On Thu, Feb 3, 2011 at 9:53 AM, Ross J. Reedstrom reeds...@rice.edu wrote: Example: upgrade_from_1_0 = '1.0 = upgrade_from_1.0.sql' upgrade_from_2_0 = '2.0 = upgrade_from_2.0.sql' upgrade_from_3_0 = '3.0 = upgrade_from_3.0.sql' Hmm, how about allowing a list of files to execute? That allows the developer to create modularized sql, and composite it in the config: for a mythical version 4.0: 1.0 = add_foobar_table.sql new_method_baz.sql 2.0 = drop_method_baz.sql add_quuz_table.sql # oops, we still needed this 3.0 = new_method_baz.sql I know when I'm developing such upgrades, the code looks like that, until I need to shoehorn them into the upgrade systems idea of version numbers matching names to find scripts to run. Good idea. My code looks that way too. -- 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] ALTER EXTENSION UPGRADE, v3
Ross J. Reedstrom reeds...@rice.edu writes: Hmm, how about allowing a list of files to execute? That allows the Sure. I still don't see why doing it in the control file is better than in the Makefile, even if it's already better than in the SQL script, at least in terms of code to write to support the idea. Speaking about which, using Make rules to prepare your upgrade files from other pieces means no development at all on the backend side. You can hardly beat that. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] out of memory during COPY .. FROM
On Tue, Feb 1, 2011 at 5:32 AM, Tom Lanyon tom+pgsql-hack...@oneshoeco.com wrote: List, Can anyone suggest where the below error comes from, given I'm attempting to load HTTP access log data with reasonably small row and column value lengths? logs=# COPY raw FROM '/path/to/big/log/file' DELIMITER E'\t' CSV; ERROR: out of memory DETAIL: Cannot enlarge string buffer containing 1073712650 bytes by 65536 more bytes. CONTEXT: COPY raw, line 613338983 It was suggested in #postgresql that I'm reaching the 1GB MaxAllocSize - but I would have thought this would only be a constraint against either large values for specific columns or for whole rows. It's worth noting that this is after 613 million rows have already been loaded (somewhere around 100GB of data) and that I'm running this COPY after the CREATE TABLE raw ... in a single transaction. I've looked at line 613338983 in the file being loaded (+/- 10 rows) and can't see anything out of the ordinary. Disclaimer: I know nothing of PostgreSQL's internals, please be gentle! Is there by any chance a trigger on this table? Or any foreign keys? What version of PostgreSQL? -- 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] Problem with postgresql database connection in combination with HUAWEI data modem
2011/1/31 Jürgen Wolfsgruber juergen.wolfsgru...@gmail.com: Trying to start a telnet-connection, the result was: telnet 127.0.0.1 5432 Trying 127.0.0.1... telnet: connect to address 127.0.0.1: Operation timed out telnet: Unable to connect to remote host That's just bizarre. How can you get a network timeout over the loopback address? Your data modem is probably doing something funky to your network stack, but I don't know what. Can you connect to PostgreSQL over a UNIX socket when the system is in this state? Does stuff continue to show up in the log file? (e.g. If you send the postmaster a SIGTERM while it's like this, do you see shutdown messages?) -- 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
[HACKERS] is_absolute_path cleanup
I have applied the attached patch to cleanup coding of the is_absolute_path macro, and added documentation of how 'E:abc' is handled on Win32. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/src/include/port.h b/src/include/port.h index 4f0c0c1..2020a26 100644 *** a/src/include/port.h --- b/src/include/port.h *** extern void pgfnames_cleanup(char **file *** 68,84 * By making this a macro we avoid needing to include path.c in libpq. */ #ifndef WIN32 #define is_absolute_path(filename) \ ( \ ! ((filename)[0] == '/') \ ) #else #define is_absolute_path(filename) \ ( \ ! ((filename)[0] == '/') || \ ! (filename)[0] == '\\' || \ (isalpha((unsigned char) ((filename)[0])) (filename)[1] == ':' \ ! ((filename)[2] == '\\' || (filename)[2] == '/')) \ ) #endif --- 68,94 * By making this a macro we avoid needing to include path.c in libpq. */ #ifndef WIN32 + #define IS_DIR_SEP(ch) ((ch) == '/') + #define is_absolute_path(filename) \ ( \ ! IS_DIR_SEP((filename)[0]) \ ) #else + #define IS_DIR_SEP(ch) ((ch) == '/' || (ch) == '\\') + + /* + * On Win32, a drive letter _not_ followed by a slash, e.g. 'E:abc', is + * relative to the cwd on that drive, or the drive's root directory + * if that drive has no cwd. Because the path itself cannot tell us + * which is the case, we have to assume the worst, i.e. that it is not + * absolute; this check is done by IS_DIR_SEP(filename[2]). + */ #define is_absolute_path(filename) \ ( \ ! IS_DIR_SEP((filename)[0]) || \ (isalpha((unsigned char) ((filename)[0])) (filename)[1] == ':' \ ! IS_DIR_SEP((filename)[2])) \ ) #endif diff --git a/src/port/path.c b/src/port/path.c index ccf801e..5b0056d 100644 *** a/src/port/path.c --- b/src/port/path.c *** *** 35,46 #ifndef WIN32 - #define IS_DIR_SEP(ch) ((ch) == '/') - #else - #define IS_DIR_SEP(ch) ((ch) == '/' || (ch) == '\\') - #endif - - #ifndef WIN32 #define IS_PATH_VAR_SEP(ch) ((ch) == ':') #else #define IS_PATH_VAR_SEP(ch) ((ch) == ';') --- 35,40 -- 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] Optimize PL/Perl function argument passing [PATCH]
On Wed, Feb 02, 2011 at 12:10:59PM -0500, Andrew Dunstan wrote: On 02/02/2011 11:45 AM, Tim Bunce wrote: But why are we bothering to keep $prolog at all any more, if all we're going to pass it isPL_sv_no all the time? Maybe we'll have a use for it in the future, but right now we don't appear to unless I'm missing something. PostgreSQL::PLPerl::NYTProf would break if it was removed, so I'd rather it wasn't. I could work around that if there's an easy way for perl code to tell what version of PostgreSQL. If there isn't I think it would be worth adding. Not really. It might well be good to add but that needs to wait for another time. Ok. I gather you're plugging in a replacement for mkfunc? Yes. For now I'll add a comment to the code saying why it's there. Thanks. Tim. -- 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] ALTER EXTENSION UPGRADE, v3
On Thu, Feb 03, 2011 at 04:31:08PM +0100, Dimitri Fontaine wrote: Ross J. Reedstrom reeds...@rice.edu writes: Hmm, how about allowing a list of files to execute? That allows the Sure. I still don't see why doing it in the control file is better than in the Makefile, even if it's already better than in the SQL script, at least in terms of code to write to support the idea. Because that's two places to touch that have to worry about mapping versions to actions. Inside the config file I'm already going to have to do that, and in mostly a trivial one-to-one mapping. The proposed make rules are an example of the kind of 'make my code match what the system wants' that I complained of. Speaking about which, using Make rules to prepare your upgrade files from other pieces means no development at all on the backend side. You can hardly beat that. Yes, from the backend-developer's perspective. But not from the extension-developer's perspective :-) And seriously, make is one of those things that is supremely capable of doing lots of stuff, but is so difficult to use correctly that everyone keeps reinventing newer wheels. Seems this one isn't round enough. In fact, doing it via make rules would still be available, if that's what floats the particular developer's boat. more choices is good. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- 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] ALTER EXTENSION UPGRADE, v3
On Thu, Feb 3, 2011 at 11:25 AM, Ross J. Reedstrom reeds...@rice.edu wrote: Speaking about which, using Make rules to prepare your upgrade files from other pieces means no development at all on the backend side. You can hardly beat that. Yes, from the backend-developer's perspective. But not from the extension-developer's perspective :-) And seriously, make is one of those things that is supremely capable of doing lots of stuff, but is so difficult to use correctly that everyone keeps reinventing newer wheels. Seems this one isn't round enough. Not to mention the fact that make doesn't work on Windows, so any extensions that rely on this will need hacks in the MSVC build system. -- 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] ALTER EXTENSION UPGRADE, v3
=?iso-8859-2?Q?=AEiga_Kranjec?= z...@ljudmila.org writes: This might be a silly idea, but I'm not sure if it was really discussed with respect to extensions here before... yes, it was ... Why not use PL/pgSQL as install/upgrade script language, specially now when it's included in the core by default? Included by default does not mean required to be present. It can be removed, and we can't have that mean breaking the extension mechanism. 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] is_absolute_path incorrect on Windows
Bruce Momjian wrote:
However, it misses the case with for example E:foo, which is a perfectly
valid path on windows. Which isn't absolute *or* relative - it's relative
to the current directory on the E: drive. Which will be the same as the
current directory for the process *if* the process current directory is
on drive E:. In other cases, it's a different directory.
I would argue that E:foo is always relative (which matches
is_absolute_path()). If E: is the current drive of the process, it is
relative, and if the current drive is not E:, it is relative to the last
current drive on E: for that process, or the top level if there was no
current drive. (Tested on XP.)
There seem to be three states:
1. absolute - already tested by is_absolute_path()
2. relative to the current directory (current drive)
3. relative on a different drive
We could probably develop code to test all three, but keep in mind that
the path itself can't distinguish between 2 and 3, and while you can
test the current drive, if the current drive changes, a 2 could become a
3, and via versa.
I have reviewed is_absolute_path() and have implemented
path_is_relative_and_below_cwd() to cleanly handle cases like 'E:abc' on
Win32; patch attached.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/adminpack/adminpack.c b/contrib/adminpack/adminpack.c
index 381554d..991affd 100644
*** a/contrib/adminpack/adminpack.c
--- b/contrib/adminpack/adminpack.c
*** convert_and_check_filename(text *arg, bo
*** 73,84
canonicalize_path(filename); /* filename can change length here */
- /* Disallow .. in the path */
- if (path_contains_parent_reference(filename))
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg(reference to parent directory (\..\) not allowed;
-
if (is_absolute_path(filename))
{
/* Allow absolute references within DataDir */
--- 73,78
*** convert_and_check_filename(text *arg, bo
*** 97,102
--- 91,100
}
else
{
+ if (!path_is_relative_and_below_cwd(filename))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg(path must be in or below the current directory;
return filename;
}
}
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index 93bc401..9774b39 100644
*** a/src/backend/utils/adt/genfile.c
--- b/src/backend/utils/adt/genfile.c
*** convert_and_check_filename(text *arg)
*** 51,62
filename = text_to_cstring(arg);
canonicalize_path(filename); /* filename can change length here */
- /* Disallow .. in the path */
- if (path_contains_parent_reference(filename))
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg(reference to parent directory (\..\) not allowed;
-
if (is_absolute_path(filename))
{
/* Allow absolute references within DataDir */
--- 51,56
*** convert_and_check_filename(text *arg)
*** 74,79
--- 68,77
}
else
{
+ if (!path_is_relative_and_below_cwd(filename))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg(path must be in or below the current directory;
return filename;
}
}
diff --git a/src/include/port.h b/src/include/port.h
index 2020a26..9a14488 100644
*** a/src/include/port.h
--- b/src/include/port.h
*** extern void join_path_components(char *r
*** 41,47
const char *head, const char *tail);
extern void canonicalize_path(char *path);
extern void make_native_path(char *path);
! extern bool path_contains_parent_reference(const char *path);
extern bool path_is_prefix_of_path(const char *path1, const char *path2);
extern const char *get_progname(const char *argv0);
extern void get_share_path(const char *my_exec_path, char *ret_path);
--- 41,47
const char *head, const char *tail);
extern void canonicalize_path(char *path);
extern void make_native_path(char *path);
! extern bool path_is_relative_and_below_cwd(const char *path);
extern bool path_is_prefix_of_path(const char *path1, const char *path2);
extern const char *get_progname(const char *argv0);
extern void get_share_path(const char *my_exec_path, char *ret_path);
*** extern void pgfnames_cleanup(char **file
*** 77,89
#else
#define IS_DIR_SEP(ch) ((ch) == '/' || (ch) == '\\')
! /*
! * On Win32, a drive letter _not_ followed by a slash, e.g. 'E:abc', is
! * relative to the cwd on that drive, or the drive's root directory
! * if that drive has no cwd. Because the path itself cannot tell us
! * which is the case, we have to assume the worst, i.e. that it is not
! * absolute; this check is done by IS_DIR_SEP(filename[2]).
! */
#define is_absolute_path(filename)
Re: [HACKERS] arrays as pl/perl input arguments [PATCH]
On Feb 3, 2011, at 4:23 AM, Alexey Klyukin wrote: - Making the conversion lazy would be a big help. Converting it to string is already lazy, and, per the argumens above, I don't see a real benefit of lazy conversion to the perl reference (as comparing to the hurdles of implementing that). I agree that we should prefer an actual array as the implementation and lazily provide a string when needed. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
Robert Haas robertmh...@gmail.com writes: Yes, from the backend-developer's perspective. But not from the extension-developer's perspective :-) And seriously, make is one of those things that is supremely capable of doing lots of stuff, but is so difficult to use correctly that everyone keeps reinventing newer wheels. Seems this one isn't round enough. Not to mention the fact that make doesn't work on Windows, so any extensions that rely on this will need hacks in the MSVC build system. Fair enough, so that's just me not seeing it. Now I agree that having the right hand side of the format I proposed be an ordered list of files rather than a single file is simple enough and comes with benefits. The examples are using spaces as the separator, how friendly is that to our windows users? Maybe using coma instead would be better? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
On Feb3, 2011, at 16:31 , Dimitri Fontaine wrote: Ross J. Reedstrom reeds...@rice.edu writes: Hmm, how about allowing a list of files to execute? That allows the Sure. I still don't see why doing it in the control file is better than in the Makefile, even if it's already better than in the SQL script, at least in terms of code to write to support the idea. Speaking about which, using Make rules to prepare your upgrade files from other pieces means no development at all on the backend side. You can hardly beat that. I fully agree. The extension infrastructure should provide basic support for upgrades, not every kind of bell and whistle one could possible think of. The bells and whistles can then be provided by the system used to *build* the extension. Not only does this keep the core infrastructure manageable, it also allows different tools to generate the update scripts to exist, each catering to the needs of different kinds of extensions. best regards, Florian Pflug -- 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] LIKE, CHAR(), and trailing spaces
Kenneth Marshall k...@rice.edu writes: On Wed, Feb 02, 2011 at 07:48:38PM -0500, Bruce Momjian wrote: It seems LIKE is considering the trailing CHAR(10) field spaces as significant, even though our documentations says: +10 for leaving the behavior as is. Yeah, we've been around on this before if memory serves. I don't think there's a case for changing it that's strong enough to outweigh backwards-compatibility considerations. Also, anyone who does want the spaces to be stripped can just add an explicit cast to text first: char_variable::text LIKE ... If we change it then we'll have to provide some other weird notation to allow people to get at the old behavior (I suppose there are some people out there relying on it). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
On Thu, Feb 3, 2011 at 11:53 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Robert Haas robertmh...@gmail.com writes: Yes, from the backend-developer's perspective. But not from the extension-developer's perspective :-) And seriously, make is one of those things that is supremely capable of doing lots of stuff, but is so difficult to use correctly that everyone keeps reinventing newer wheels. Seems this one isn't round enough. Not to mention the fact that make doesn't work on Windows, so any extensions that rely on this will need hacks in the MSVC build system. Fair enough, so that's just me not seeing it. Now I agree that having the right hand side of the format I proposed be an ordered list of files rather than a single file is simple enough and comes with benefits. The examples are using spaces as the separator, how friendly is that to our windows users? Maybe using coma instead would be better? Comma would be better. There is even some backend code that will tokenize on it, I think. -- 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] is_absolute_path incorrect on Windows
Bruce Momjian br...@momjian.us writes: I have reviewed is_absolute_path() and have implemented path_is_relative_and_below_cwd() to cleanly handle cases like 'E:abc' on Win32; patch attached. This patch appears to remove some security-critical restrictions. Why did you delete the path_contains_parent_reference calls? 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] ALTER EXTENSION UPGRADE, v3
Florian Pflug f...@phlo.org writes: I fully agree. The extension infrastructure should provide basic support for upgrades, not every kind of bell and whistle one could possible think of. Maybe somewhere around here we should stop and ask why we are bothering with any of this. The original idea for an extension concept was that (1) some collection of objects could be designated as a module (2) pg_dump would be taught to dump LOAD MODULE foo instead of the individual objects (3) the way you'd do an upgrade is to dump and reload into a database that has available a newer definition of the module's content. Given that pg_upgrade is now considered a supported piece of the system, ISTM that most real-world upgrade scenarios will be accomplished with pg_upgrade relying on provision (3). It looks to me like we're talking about adding a large amount of complication --- both for the core database and for module authors --- in order to provide a duplicate solution for that. Why should we bother? Especially, why should we bother in version 1 of the feature? This could all be added later if we determine there's really sufficient demand, but right now we have no experience to show whether there is demand or not. 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] ALTER EXTENSION UPGRADE, v3
On Feb 3, 2011, at 9:38 AM, Tom Lane wrote: Given that pg_upgrade is now considered a supported piece of the system, ISTM that most real-world upgrade scenarios will be accomplished with pg_upgrade relying on provision (3). It looks to me like we're talking about adding a large amount of complication --- both for the core database and for module authors --- in order to provide a duplicate solution for that. Why should we bother? Especially, why should we bother in version 1 of the feature? This could all be added later if we determine there's really sufficient demand, but right now we have no experience to show whether there is demand or not. Given the level of disagreement, I think that leaving upgrades aside for now may be prudent, especially since there are other ways to do it (none very convenient, but no worse than what we have right now, and in the case of pg_dump, better). I think we will need to come back to it before, long, however, because many extensions are released far more often than major versions of PostgreSQL. So while one might run pg_upgrade, at most, about once every 12-18 months, they will often want to take advantage of the features of extensions on a much more ambitious release schedule. Extension upgrades need to be done eventually to make it easier to manage extension release schedules independent of PostgreSQL core upgrades. Otherwise, you're just going to get more patches for contrib. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
On Thu, Feb 3, 2011 at 12:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Florian Pflug f...@phlo.org writes: I fully agree. The extension infrastructure should provide basic support for upgrades, not every kind of bell and whistle one could possible think of. Maybe somewhere around here we should stop and ask why we are bothering with any of this. The original idea for an extension concept was that (1) some collection of objects could be designated as a module (2) pg_dump would be taught to dump LOAD MODULE foo instead of the individual objects (3) the way you'd do an upgrade is to dump and reload into a database that has available a newer definition of the module's content. Given that pg_upgrade is now considered a supported piece of the system, ISTM that most real-world upgrade scenarios will be accomplished with pg_upgrade relying on provision (3). It looks to me like we're talking about adding a large amount of complication --- both for the core database and for module authors --- in order to provide a duplicate solution for that. Why should we bother? Especially, why should we bother in version 1 of the feature? This could all be added later if we determine there's really sufficient demand, but right now we have no experience to show whether there is demand or not. I think you can pretty much take it to the bank that there will be demand. This is an important, real-world problem. That having been said, I'm not 100% convinced that the main extensions patch is ready for prime-time, and I'm even less convinced that the upgrade patch is anywhere the point where we want to commit to it long-term. So I would have no qualms about punting it out to 9.2. -- 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] arrays as pl/perl input arguments [PATCH]
On Thu, Feb 03, 2011 at 02:23:32PM +0200, Alexey Klyukin wrote:
Hi,
On Feb 2, 2011, at 7:16 PM, Tim Bunce wrote:
I'm sorry I'm late to this party. I haven't been keeping up with
pgsql-hackers.
Better late than never :)
I'd kind'a hoped that this functionality could be tied into extending
PL/Perl to handle named arguments. That way the perl variables
corresponding to the named arguments could be given references without
breaking any code.
Franky I don't see a direct connection between conversion of arrays
into array references and supporting named arguments.
There is no direct connection. I wasn't clear, tied was too strong a
word for it.
Could you, please, show some examples of how you hoped the
functionality could be extended?
I wasn't suggesting extending the functionality, just a way of adding
the functionality that wouldn't risk impacting existing code.
Imagine that PL/Perl could handle named arguments:
CREATE FUNCTION join_list( separator text, list array ) AS $$
return join( $separator, @$list );
$$ LANGUAGE plperl;
The $list variable, magically created by PL/Perl, could be the array
reference created by your code, without altering the contents of @_.
Existing code that does the traditional explicit unpacking of @_
wouldn't be affected. So there would be no need for the string overload
hack which, although I suggested it, I'm a little uncomfortable with.
(The problems with recursion and the need for is_array_ref with
hardwired class names are a little troubling. Not to mention the
extra overheads in accessing the array.)
On the ther hand, a string version of the array would still need to be
created for @_.
Some observations on the current code (based on a quick skim):
- I'd like to see the conversion function exposed as a builtin
$ref = decode_array_literal({...});
In principal, I think that's not hard to built with the functionality provided
by this patch. I see this as an XS function which takes the array string,
calls the array_in to convert it to the array datum, and, finally, calls
plperl_ref_from_pg_array (provided by the patch) to produce the resulting
array reference.
I think that would be useful.
- Every existing plperl function that takes arrays is going to get
slower due to the overhead of parsing the string and allocating the
array and all its elements.
Well, per my understanding of Alex changes, the string parsing is not invoked
unless requested by referencing the array in a string context. Normally, onle
plperl_ref_from_pg_array will be invoked every time the function is called
with an array argument, which would take little time to convert the PostgreSQL
internal array representation (not a string) to the perl references, but
that's
no different from what is already done with composite type arguments, which
are converted to perl hash references on every corresponding function call.
I'd missed that it was using the internal array representation (obvious
in hindsight) but there's still a significant cost in allocating the SVs
that won't be used by existing code. Though I agree it's of the same
order as for composite types.
- Some of those functions may not use the array at all and some may
simply pass it on as an argument to another function.
I don't see how it would be good to optimize for the functions that are
declared to get the array but in fact do nothing with it. And considering the
case of passing an array through to another function, it's currently not
possible to call another pl/perl function from an existing one directly, and
when passing muti-dimensional arrays to a perl function one would need to
convert it to the array references anyway.
I was thinking of calls to other pl/perl functions via sql.
- Making the conversion lazy would be a big help.
Converting it to string is already lazy, and, per the argumens above, I don't
see a real benefit of lazy conversion to the perl reference (as comparing to
the hurdles of implementing that).
I (now) agree. Thanks.
Tim.
--
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] ALTER EXTENSION UPGRADE, v3
On Feb 3, 2011, at 9:54 AM, Robert Haas wrote: I think you can pretty much take it to the bank that there will be demand. This is an important, real-world problem. That having been said, I'm not 100% convinced that the main extensions patch is ready for prime-time, and I'm even less convinced that the upgrade patch is anywhere the point where we want to commit to it long-term. So I would have no qualms about punting it out to 9.2. I think that the core extension stuff and pg_dump stuff is pretty close. It's the upgrade stuff that's thorny. And really, this is a pretty well-known problem. Surely someone has done some research to solve it? Anyway, IMHO, try to get CREATE EXTENSION into 9.1, perhaps put off ALTER EXTENSION UPGRADE to 9.2. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
David E. Wheeler da...@kineticode.com writes: I think we will need to come back to it before, long, however, because many extensions are released far more often than major versions of PostgreSQL. So while one might run pg_upgrade, at most, about once every 12-18 months, they will often want to take advantage of the features of extensions on a much more ambitious release schedule. Well, pg_upgrade is designed to work within a major-version series, eg you could do a 9.1-to-9.1 upgrade if you needed to install a newer version of an extension. Admittedly, this is swinging a rather larger hammer than apply an upgrade script would entail. But I'm still not convinced that we need to expend a great deal of work on making that process a tad more efficient. Now having said that, it does occur to me that there is an upgrade-ish scenario that every user is going to hit immediately, which is how to get from an existing installation with a pile of loose objects created by one or more contrib modules to a state where those objects are understood to be parts of modules. But that is a special case that perhaps deserves a special-case solution, rather than inventing a very large wheel. 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] ALTER EXTENSION UPGRADE, v3
On Feb 3, 2011, at 10:07 AM, Tom Lane wrote: Well, pg_upgrade is designed to work within a major-version series, eg you could do a 9.1-to-9.1 upgrade if you needed to install a newer version of an extension. Admittedly, this is swinging a rather larger hammer than apply an upgrade script would entail. Dude. That's a frigging piledriver! But I'm still not convinced that we need to expend a great deal of work on making that process a tad more efficient. Agreed. I would handle simple extension upgrades not with pg_upgrade, but the same way I do now. Think about how one currently jumps from PostGIS 1.4 to 1.5. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] is_absolute_path incorrect on Windows
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I have reviewed is_absolute_path() and have implemented path_is_relative_and_below_cwd() to cleanly handle cases like 'E:abc' on Win32; patch attached. This patch appears to remove some security-critical restrictions. Why did you delete the path_contains_parent_reference calls? They are now in path_is_relative_and_below_cwd(), and I assume we can allow .. for an absolute path in these cases, i.e. it has to match the data or log path we defined, and I don't see a general reason to prevent .. in absolute paths, only relative ones. -- 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] arrays as pl/perl input arguments [PATCH]
On 02/03/2011 01:01 PM, Tim Bunce wrote: Imagine that PL/Perl could handle named arguments: CREATE FUNCTION join_list( separator text, list array ) AS $$ return join( $separator, @$list ); $$ LANGUAGE plperl; The $list variable, magically created by PL/Perl, could be the array reference created by your code, without altering the contents of @_. I think that's getting way too subtle, and it would probably violate the POLA. If we implement named arguments I would expect $list to be the same as $_[0] - Every existing plperl function that takes arrays is going to get slower due to the overhead of parsing the string and allocating the array and all its elements. Well, per my understanding of Alex changes, the string parsing is not invoked unless requested by referencing the array in a string context. Normally, onle plperl_ref_from_pg_array will be invoked every time the function is called with an array argument, which would take little time to convert the PostgreSQL internal array representation (not a string) to the perl references, but that's no different from what is already done with composite type arguments, which are converted to perl hash references on every corresponding function call. I'd missed that it was using the internal array representation (obvious in hindsight) but there's still a significant cost in allocating the SVs that won't be used by existing code. Though I agree it's of the same order as for composite types. Well, the question seems to be whether or not it's a reasonable price to pay. On the whole I'm inclined to think it is, especially when it can be avoided by updating your code, which will be a saving in fragility and complexity as well. 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] ALTER EXTENSION UPGRADE, v3
Tom Lane t...@sss.pgh.pa.us writes: Maybe somewhere around here we should stop and ask why we are bothering with any of this. The original idea for an extension concept was that (1) some collection of objects could be designated as a module (2) pg_dump would be taught to dump LOAD MODULE foo instead of the individual objects (3) the way you'd do an upgrade is to dump and reload into a database that has available a newer definition of the module's content. That upgrade solution is fine in some cases for major upgrades. That's not what we're talking about here, though. Extension release schedule is to be quite different from PostgreSQL one. Given that pg_upgrade is now considered a supported piece of the system, ISTM that most real-world upgrade scenarios will be accomplished with pg_upgrade relying on provision (3). It looks to me like we're talking about adding a large amount of complication --- both for the core database and for module authors --- in order to provide a duplicate solution for that. Why should we bother? Especially, why should we Think in particular about in-house extensions: a collection of PL code that's not maintained in the database but in some SCM, typically. You keep maintaining your application, and the PL code too, and you keep wanting to roll out those changes to production. Having a way to package that then ALTER EXTENSION myappplcode UPGRADE; sounds nice to me. Nothing to do with PostgreSQL upgrades, though, really. bother in version 1 of the feature? This could all be added later if we determine there's really sufficient demand, but right now we have no experience to show whether there is demand or not. The reason why I've been bothering for version 1 is so that we can support people upgrading from a previous version of PostgreSQL with extensions installed, and who will want that both \dx and pg_dump behaves correctly from day-1 in 9.1. We will need to bootstrap the process some day, first extension enabled release sounded fine from here. Nobody will want to DROP TYPE hstore CASCADE; then repair, all just so that they'll able to have a better pg_dump next time. Now, current patch to do that is rather small if you don't include the new contrib scripts. First line is src only, second one src+doc: 22 files changed, 946 insertions(+), 69 deletions(-) 40 files changed, 1352 insertions(+), 79 deletions(-) Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] arrays as pl/perl input arguments [PATCH]
On tor, 2011-02-03 at 18:01 +, Tim Bunce wrote: Imagine that PL/Perl could handle named arguments: CREATE FUNCTION join_list( separator text, list array ) AS $$ return join( $separator, @$list ); $$ LANGUAGE plperl; The $list variable, magically created by PL/Perl, could be the array reference created by your code, without altering the contents of @_. I would find that pretty surprising, since Perl itself doesn't even provide named arguments. -- 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] ALTER EXTENSION UPGRADE, v3
Tom Lane t...@sss.pgh.pa.us writes: Now having said that, it does occur to me that there is an upgrade-ish scenario that every user is going to hit immediately, which is how to get from an existing installation with a pile of loose objects created by one or more contrib modules to a state where those objects are understood to be parts of modules. But that is a special case that perhaps deserves a special-case solution, rather than inventing a very large wheel. Well a good deal of the code I've written in the UGPRADE patch is there for this special case, that's ALTER OBJECT ... SET EXTENSION ...; This allows to attach any existing object to a given existing extension. Now what you need is a way to create an empty extension so that you can attach objects to it. That's in the patch in the form of the new command CREATE WRAPPER EXTENSION ...; WRAPPER was the most convenient keyword we already have I found. Then, there's only 2 things left in the patch. The contrib scripts that make that happen, and the control file support so that the command ALTER EXTENSION $contrib UPGRADE will run the upgrade script. This mechanism has been made in a way that allows it to cover running other scripts for other kind of upgrades. That's about it. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] is_absolute_path incorrect on Windows
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I have reviewed is_absolute_path() and have implemented path_is_relative_and_below_cwd() to cleanly handle cases like 'E:abc' on Win32; patch attached. This patch appears to remove some security-critical restrictions. Why did you delete the path_contains_parent_reference calls? They are now in path_is_relative_and_below_cwd(), ... and thus not invoked in the absolute-path case. This is a security hole. I don't see a general reason to prevent .. in absolute paths, only relative ones. load '/path/to/database/../../../path/to/anywhere' 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] ALTER EXTENSION UPGRADE, v3
On Thu, Feb 3, 2011 at 1:10 PM, David E. Wheeler da...@kineticode.com wrote: On Feb 3, 2011, at 10:07 AM, Tom Lane wrote: Well, pg_upgrade is designed to work within a major-version series, eg you could do a 9.1-to-9.1 upgrade if you needed to install a newer version of an extension. Admittedly, this is swinging a rather larger hammer than apply an upgrade script would entail. Dude. That's a frigging piledriver! That's putting it mildly. It's more like sending a rocket into outer space to tweak the orbit of a comet so that it crashes into your barbecue grill to light a fire so you can roast marshmallows. -- 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] ALTER EXTENSION UPGRADE, v3
Robert Haas robertmh...@gmail.com writes: On Thu, Feb 3, 2011 at 1:10 PM, David E. Wheeler da...@kineticode.com wrote: On Feb 3, 2011, at 10:07 AM, Tom Lane wrote: Well, pg_upgrade is designed to work within a major-version series, eg you could do a 9.1-to-9.1 upgrade if you needed to install a newer version of an extension. Admittedly, this is swinging a rather larger hammer than apply an upgrade script would entail. Dude. That's a frigging piledriver! That's putting it mildly. It's more like sending a rocket into outer space to tweak the orbit of a comet so that it crashes into your barbecue grill to light a fire so you can roast marshmallows. No, it's more like using a sledgehammer to swat a fly because you don't have a flyswatter and aren't inclined to drive ten miles to buy one. It'll get the job done, and the added cost of building/maintaining a more finely calibrated tool may well outweigh the value. 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] ALTER EXTENSION UPGRADE, v3
On Feb 3, 2011, at 11:02 AM, Tom Lane wrote: That's putting it mildly. It's more like sending a rocket into outer space to tweak the orbit of a comet so that it crashes into your barbecue grill to light a fire so you can roast marshmallows. LOL. No, it's more like using a sledgehammer to swat a fly because you don't have a flyswatter and aren't inclined to drive ten miles to buy one. It'll get the job done, and the added cost of building/maintaining a more finely calibrated tool may well outweigh the value. I'd rather put down the sledgehammer and pick up a leaf or a paper bag or something. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
On Thu, Feb 3, 2011 at 2:02 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Feb 3, 2011 at 1:10 PM, David E. Wheeler da...@kineticode.com wrote: On Feb 3, 2011, at 10:07 AM, Tom Lane wrote: Well, pg_upgrade is designed to work within a major-version series, eg you could do a 9.1-to-9.1 upgrade if you needed to install a newer version of an extension. Admittedly, this is swinging a rather larger hammer than apply an upgrade script would entail. Dude. That's a frigging piledriver! That's putting it mildly. It's more like sending a rocket into outer space to tweak the orbit of a comet so that it crashes into your barbecue grill to light a fire so you can roast marshmallows. No, it's more like using a sledgehammer to swat a fly because you don't have a flyswatter and aren't inclined to drive ten miles to buy one In other words, it's something no sensible person actually does? It'll get the job done, and the added cost of building/maintaining a more finely calibrated tool may well outweigh the value. It'll get the job done for very small values of getting the job done. Let's suppose that version 2 of hstore comes out, improving on version 1 of hstore by adding one new useful function. Your proposed solution is that this person should initdb a new cluster, shut down their database, pg_upgrade over to the new cluster, and start it back up again to get that function definition. What's actually going to happen in 99.44% of cases is that the person is going to say this extension mechanism sucks and create the function definition by hand, because even if their database is unimportant enough that they don't mind the downtime, that's a ridiculous amount of hassle for what ought to be a straightforward operation. The reason for possibly postponing this to 9.2 is not that it isn't necessary but that we might not yet be sure what the best way to do it is. -- 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] ALTER EXTENSION UPGRADE, v3
All, Let me summarize the prospective solutions based on some chatting with some potential extension authors (that is, folks who maintain in-house stuff they're thinking of offering as extensions). Especially since I think at this point the majority of -hackers has lost track of the argument: (A) Writing a separate upgrade script between each two versions This is completely unrealistic. No module maintainer will ever do this. Imagine an extension which had 20 releases over 4 years; that would be *190* upgrade scripts. Any solution we come up with cannot require module maintainers to write more than one upgrade script per release, or we will have no module maintainers. (B) As (A), but with in-script Includes so that eariler versions of scripts could be re-used for later version upgrades or strung together. This is somewhat more realistic, given that it could be done automatically and then tweaked. Especially if we supply a script to generate version upgrade scripts. (C) as (A), but through concatinating scripts for upgrade using Make files. This seems like the worst of all possible solutions. First, it prevents us from ever having a binary module release network, and permanently requires anyone using extensions to have GNU make present and accessible on their system, thus pretty much leaving out the Windows users forever. Second, it's a lot harder for module maintainers to tweak than includes would be, especially for SQL-only modules. Third, it requires Make to check what version you currently have installed in order to build the module, something which is unlikely to be reliable. (D) Requires a series of ordered upgrade scripts in sortable version numbering, each of which gets applied in order between the two versions. This initially seems like the most attractive option -- and is the one used by dozens of popular open source web applications -- but has some major roadblocks for us. First, it requires module authors to subscribe to a uniform sortable versions scheme (which isn't a bad thing, the users would certainly appreciate it, and PGXN is liable to enforce this anyway). Second and much more substantially, .so's installed for later versions might be incompatible with intermediate upgrade scripts, and intermediate .so's are unlikely to be present. (E) Involves relying on pg_upgrade. In addition to the sledgehammer issue, I really don't see how this would work *at all*. First, modules would almost by definition have a release cycle which is independant of PostgreSQL core, and many versions of modules would work with several versions of PostgreSQL. Second, pg_upgrade is currently unable to upgrade user-owned objects at all, so I don't see how it would be handling modules. Thirdly, pg_upgrade does not run scripts, so required steps for some module upgrades, like say rebuilding an index or replacing a data type, could not be handled at all. Finally, if we modify pg_upgrade to handle extensions, we're liable to break it. Have I summed up the options? Did I miss anything? Note that handling upgrades of complex applications is not a problem which anyone in the world has solved that I know of. So it's unsurprising that we're having difficulty with it. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
Josh Berkus j...@agliodbs.com writes: Let me summarize the prospective solutions based on some chatting with some potential extension authors (that is, folks who maintain in-house stuff they're thinking of offering as extensions). Especially since I think at this point the majority of -hackers has lost track of the argument: Thanks for doing that! (A) Writing a separate upgrade script between each two versions (B) As (A), but with in-script Includes so that eariler versions of scripts could be re-used for later version upgrades or strung together. Well if you want to support upgrades between each two versions, that means you have users and you don't know what they currently have installed. Then you have this problem to solve, and it's complex the same no matter what tools are offered. (C) as (A), but through concatinating scripts for upgrade using Make files. This seems like the worst of all possible solutions. First, it prevents us from ever having a binary module release network, and permanently requires anyone using extensions to have GNU make present and accessible on their system, thus pretty much leaving out the Windows users forever. Second, it's a lot harder for module maintainers to tweak than includes would be, especially for SQL-only modules. Third, it requires Make to check what version you currently have installed in order to build the module, something which is unlikely to be reliable. You're missing something here. In this scheme, make is only used to prepare the upgrade scripts. Then you package and ship those, and you don't need make no more, even when the target is windows. More than that, the tool to use would be `cat`, really, Make would just call it on files in the right order. A perl one-liner will certainly do just fine. So in fact this is just saying the authors to manage the situation as they wish, then setup the control file with the produced scripts references to use. That's it. (D) Requires a series of ordered upgrade scripts in sortable version numbering, each of which gets applied in order between the two versions. Well, your build process is certainly powerful enough to concatenate file content together, right? Note that handling upgrades of complex applications is not a problem which anyone in the world has solved that I know of. So it's unsurprising that we're having difficulty with it. Agreed. So my proposal was, again, that we don't solve it this year but provide a mechanism that allows extension authors to setup which script to run when the user will ALTER EXTENSION foo UPGRADE. How they come up with such a script, I say, is *NOT* our problem. At least for 9.1. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] arrays as pl/perl input arguments [PATCH]
On Thu, Feb 3, 2011 at 05:23, Alexey Klyukin al...@commandprompt.com wrote:
Hi,
On Feb 2, 2011, at 7:16 PM, Tim Bunce wrote:
[...]
Some observations on the current code (based on a quick skim):
- I'd like to see the conversion function exposed as a builtin
$ref = decode_array_literal({...});
In principal, I think that's not hard to built with the functionality provided
by this patch. I see this as an XS function which takes the array string,
*cough* array string and Oid for the datatype right? :P
calls the array_in to convert it to the array datum, and, finally, calls
plperl_ref_from_pg_array (provided by the patch) to produce the resulting
array reference.
- Every existing plperl function that takes arrays is going to get
slower due to the overhead of parsing the string and allocating the
array and all its elements.
Well, per my understanding of Alex changes, the string parsing is not invoked
unless requested by referencing the array in a string context.
Sorry, there does seem to be some confusion here. The first version I
posted did lazy conversion to a string using encode_array_literal().
Unfortunately that function falls over with row/composite types. I
don't see a way to quote those without knowing the datatype, so in
interest of having it be 'correct' instead of fast, I made it always
decode to an array ref _and_ string in the next version :-(.
I see a couple of ways to fix this:
1) Using PTR2IV store the datum pointer in the array pseudo object.
This way when used as a string we have the original Datum and can call
the correct OutputFunction on demand. It would also let us call
plperl_ref_from_pg_array lazily. I have not actually tried it, but it
should work.
2) Add encode_composite_literal($hashref, $typeoid) and store the
type/Oid in the pseudo object. We could then call that lazily from the
perl.
3) Add the column position to the pseudo object and quote
appropriately. Simpler than #1 or #2 but not as robust.
4) Maybe there is a way of setting composite columns by column instead
of by position? If so we can encode that way. However everything on
http://www.postgresql.org/docs/current/interactive/rowtypes.html that
has to do with the 'literal' format seems to be positional.
5) Decided its worth the performance hit to always decode to both. (
Note it may not be as big as people think, in the case that you return
the array reference we have to convert it to a string anyway )
--
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] ALTER EXTENSION UPGRADE, v3
Well if you want to support upgrades between each two versions, that means you have users and you don't know what they currently have installed. Then you have this problem to solve, and it's complex the same no matter what tools are offered. How *are* we detecting which version is installed, anyway? Is that in the pg_extenstions table? You're missing something here. In this scheme, make is only used to prepare the upgrade scripts. Then you package and ship those, and you don't need make no more, even when the target is windows. More than that, the tool to use would be `cat`, really, Make would just call it on files in the right order. A perl one-liner will certainly do just fine. Ah, I see. So you're proposing a build system for the 100's of verison-to-version upgrade scripts. That makes a lot more sense, although I wonder what such a build script would look like in actuality. Agreed. So my proposal was, again, that we don't solve it this year but provide a mechanism that allows extension authors to setup which script to run when the user will ALTER EXTENSION foo UPGRADE. How they come up with such a script, I say, is *NOT* our problem. At least for 9.1. So every package would include a script called upgrade.sql ( or upgrade.c? ) which is supposed to handle the upgrade, and it's up to the module author to power that, at least until 9.2? Seem like the most reasonable course for February ... -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] keeping a timestamp of the last stats reset (for a db, table and function)
Dne 30.1.2011 23:22, Robert Haas napsal(a):
On Thu, Dec 23, 2010 at 2:41 PM, Tomas Vondra t...@fuzzy.cz wrote:
OK, so here goes the simplified patch - it tracks one reset timestamp
for a background writer and for each database.
I think you forgot the attachment.
Yes, I did. Thanks!
This patch no longer applies. Please update.
I've updated the patch - rebased to the current HEAD.
regards
Tomas
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
***
*** 663,668 postgres: replaceableuser/ replaceabledatabase/
replaceablehost/ re
--- 663,677
/row
row
+
entryliteralfunctionpg_stat_get_db_stat_reset_time/function(typeoid/type)/literal/entry
+ entrytypetimestamptz/type/entry
+ entry
+Time of the last reset of statistics for this database (as a result of
executing
+functionpg_stat_reset/function function) or any object within it
(table or index).
+ /entry
+ /row
+
+ row
entryliteralfunctionpg_stat_get_numscans/function(typeoid/type)/literal/entry
entrytypebigint/type/entry
entry
***
*** 1125,1130 postgres: replaceableuser/ replaceabledatabase/
replaceablehost/ re
--- 1134,1148
varnamebgwriter_lru_maxpages/varname parameter
/entry
/row
+
+ row
+
entryliteralfunctionpg_stat_get_bgwriter_stat_reset_time()/function/literal/entry
+ entrytypetimestamptz/type/entry
+ entry
+ Time of the last reset of statistics for the background writer (as a
result of executing
+ functionpg_stat_reset_shared('bgwriter')/function)
+ /entry
+ /row
row
entryliteralfunctionpg_stat_get_buf_written_backend()/function/literal/entry
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
***
*** 523,529 CREATE VIEW pg_stat_database AS
pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
! pg_stat_get_db_conflict_all(D.oid) AS conflicts
FROM pg_database D;
CREATE VIEW pg_stat_database_conflicts AS
--- 523,530
pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
! pg_stat_get_db_conflict_all(D.oid) AS conflicts,
! pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
FROM pg_database D;
CREATE VIEW pg_stat_database_conflicts AS
***
*** 570,576 CREATE VIEW pg_stat_bgwriter AS
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_written_backend() AS buffers_backend,
pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
! pg_stat_get_buf_alloc() AS buffers_alloc;
CREATE VIEW pg_user_mappings AS
SELECT
--- 571,578
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_written_backend() AS buffers_backend,
pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
! pg_stat_get_buf_alloc() AS buffers_alloc,
! pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
CREATE VIEW pg_user_mappings AS
SELECT
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
***
*** 3160,3165 pgstat_get_db_entry(Oid databaseid, bool create)
--- 3160,3167
result-n_conflict_bufferpin = 0;
result-n_conflict_startup_deadlock = 0;
+ result-stat_reset_timestamp = GetCurrentTimestamp();
+
memset(hash_ctl, 0, sizeof(hash_ctl));
hash_ctl.keysize = sizeof(Oid);
hash_ctl.entrysize = sizeof(PgStat_StatTabEntry);
***
*** 3438,3443 pgstat_read_statsfile(Oid onlydb, bool permanent)
--- 3440,3451
* load an existing statsfile.
*/
memset(globalStats, 0, sizeof(globalStats));
+
+ /*
+* Set the current timestamp (will be kept only in case we can't load an
+* existing statsfile.
+*/
+ globalStats.stat_reset_timestamp = GetCurrentTimestamp();
/*
* Try to open the status file. If it doesn't exist, the backends simply
***
*** 4052,4057 pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int
len)
--- 4060,4067
dbentry-n_tuples_deleted = 0;
dbentry-last_autovac_time = 0;
+ dbentry-stat_reset_timestamp = GetCurrentTimestamp();
+
memset(hash_ctl, 0, sizeof(hash_ctl));
hash_ctl.keysize = sizeof(Oid);
hash_ctl.entrysize = sizeof(PgStat_StatTabEntry);
***
*** 4083,4088
Re: [HACKERS] Spread checkpoint sync
On Sat, Jan 15, 2011 at 05:47:24AM -0500, Greg Smith wrote: For example, the pre-release Squeeze numbers we're seeing are awful so far, but it's not really done yet either. Unfortunately, it does not look like Debian squeeze will change any more (or has changed much since your post) at this point, except for maybe further stable kernel updates. Which file system did you see those awful numbers on and could you maybe go into some more detail? Thanks, Michael -- marco_g I did send an email to propose multithreading to grub-devel on the first of april. marco_g Unfortunately everyone thought I was serious ;-) -- 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] ALTER EXTENSION UPGRADE, v3
Josh Berkus j...@agliodbs.com writes: (D) Requires a series of ordered upgrade scripts in sortable version numbering, each of which gets applied in order between the two versions. This initially seems like the most attractive option -- and is the one used by dozens of popular open source web applications -- but has some major roadblocks for us. First, it requires module authors to subscribe to a uniform sortable versions scheme (which isn't a bad thing, the users would certainly appreciate it, and PGXN is liable to enforce this anyway). Second and much more substantially, .so's installed for later versions might be incompatible with intermediate upgrade scripts, and intermediate .so's are unlikely to be present. FWIW, I think that last objection is bogus. There's no reason that an extension author can't leave dummy C functions in his code to support obsolete CREATE FUNCTION calls. (As an example, I added one to Alexander Korotkov's recent pg_trgm patch. It consists of 10 lines of boilerplate code, and could have been less if I'd used a quick and dirty elog() instead of ereport().) This is certainly a lot less of a problem than the difficulties with the other approaches. I think some of your objections to the pg_upgrade approach are equally bogus. In particular, I don't believe any of these approaches will usefully serve cases where indexes have to be rebuilt to be compatible with a new .so. Those indexes won't all be in the same database, and even if they were, no simple SQL script is going to be able to find them. If an extension author wants to break on-disk compatibility, it's going to be just as unfriendly to his users as such a break in the core database will be, ie, they're going to have to do dump and reload. The extension mechanism can't be expected to solve 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
Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
On Feb 3, 2011, at 11:35 AM, Josh Berkus wrote: (D) Requires a series of ordered upgrade scripts in sortable version numbering, each of which gets applied in order between the two versions. This initially seems like the most attractive option -- and is the one used by dozens of popular open source web applications -- but has some major roadblocks for us. First, it requires module authors to subscribe to a uniform sortable versions scheme (which isn't a bad thing, the users would certainly appreciate it, and PGXN is liable to enforce this anyway). PGXN does enforce Semantic Versions (http://semver.org/), but extensions wont' be limited to PGXN, of course. Might be a lot of stuff developed for internal use in organizations, and they surely won't use the same version numbers. Agreed with your summary, well put. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
FWIW, I think that last objection is bogus. There's no reason that an extension author can't leave dummy C functions in his code to support obsolete CREATE FUNCTION calls. (As an example, I added one to Alexander Korotkov's recent pg_trgm patch. It consists of 10 lines of boilerplate code, and could have been less if I'd used a quick and dirty elog() instead of ereport().) This is certainly a lot less of a problem than the difficulties with the other approaches. Well, that makes solution (D) a lot more viable then. I think some of your objections to the pg_upgrade approach are equally bogus. In particular, I don't believe any of these approaches will usefully serve cases where indexes have to be rebuilt to be compatible with a new .so. Those indexes won't all be in the same database, and even if they were, no simple SQL script is going to be able to find them. If an extension author wants to break on-disk compatibility, it's going to be just as unfriendly to his users as such a break in the core database will be, ie, they're going to have to do dump and reload. The extension mechanism can't be expected to solve that. I could do it, given an extension upgrade script which could run PL/pgSQL code. That is, I could write a script which finds all indexes dependant on a particular data type and reindex them. So I disagree that it can't be solved. It just can't be solved *by pg_upgrade*. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Per-column collation, the finale
On Thu, Feb 03, 2011 at 05:53:28PM +0200, Peter Eisentraut wrote: On tor, 2011-02-03 at 00:10 -0500, Noah Misch wrote: This is good stuff. I'll send you a new patch in a day or three for perhaps another round of performance tests. Some of the other issues above can perhaps be postponed for follow-up patches. I agree -- if the performance-when-unused gets solid, none of my other comments ought to hold things up. Here is a new patch. The main change is in varstr_cmp(), avoiding the calls to pg_newlocale_from_collation() when the default locale is used. This accounts for the performance regression in my tests. It also addresses some of your refactoring ideas. Looks good and tests well. I've attached the same benchmark script with updated timings, and I've marked the patch Ready for Committer. nm -- Setup -- SELECT setseed(0); -- CREATE TABLE t (c) AS -- SELECT chr(1 + (random() * 65534)::int) FROM generate_series(1,1000) gen(n); -- NOTE: due to concurrent load, timings typically varied 1-2s between runs. -- en_US.UTF8, unpatched: 68.75s -- en_US.UTF8, patched: 67.95s -- id_ID.UTF8, unpatched: 67.48s -- id_ID.UTF8, patched: 69.34s SELECT min(c) -- en_US.UTF8, patched: 81.59s -- id_ID.UTF8, patched: 84.59s --SELECT min(c COLLATE id_ID) FROM ( SELECT c FROM t UNION ALL SELECT c FROM t UNION ALL SELECT c FROM t UNION ALL SELECT c FROM t UNION ALL SELECT c FROM t UNION ALL SELECT c FROM t UNION ALL SELECT c FROM t UNION ALL SELECT c FROM t ) t_all; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] exposing COPY API
Revisiting this, it occurred to me that I could achieve what I need of we extend the proposed API a bit. Currently, it has: extern CopyState BeginCopyFrom(Relation rel, const char *filename, List *attnamelist, List *options); I'd like to be able to add a callback function to construct the values for the tuple. So it would become something like: typedef void (*copy_make_values) (CopyState cstate, NumFieldsRead int); extern CopyState BeginCopyFrom(Relation rel, const char *filename, List *attnamelist, List *options, copy_make_values custom_values_func); If custom_values_func were NULL (as it would be if using the builtin COPY), then the builtin code would be run to construct the values for making tuple. If not null, the function would be called. Of course, I want this so I could construct a text array from the read in data, but I could also imagine a foreign data wrapper wanting to mangle the data before handing it to postgres, say by filling in a field or hashing it. The intrusiveness of this would be very small, I think. Thoughts? 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] arrays as pl/perl input arguments [PATCH]
On Mon, Jan 31, 2011 at 01:34, Alexey Klyukin al...@commandprompt.com wrote:
I've looked at the patch and added a test for arrays exceeding or equal
maximum dimensions to check, whether the recursive function won't bring
surprises there. I've also added check_stack_depth calls to both split_array
and plperl_hash_from_tuple. Note that the regression fails currently due to
the incorrect error reporting in
PostgreSQL, per
http://archives.postgresql.org/pgsql-hackers/2011-01/msg02888.php.
The v5 is attached.
One thing I find odd is we allow for nested arrays, but not nested
composites. For example:
= create type foo as (a int[]);
= create type foofoo as (b foo);
= create or replace function fa(foofoo[]) returns void as $$
my $foofoo_array = shift;
warn ref $foofoo_array-[0] || 'SCALAR';
warn ref $foofoo_array-[0]-{'b'} || 'SCALAR';
$$ language plperl;
= select fa(ARRAY[ROW(ROW(ARRAY[1]))]::foofoo[]);
WARNING: HASH at line 3.
CONTEXT: PL/Perl function fa
WARNING: SCALAR at line 4.
CONTEXT: PL/Perl function fa
Why is foofoo.b a scalar but foofoo is a hash? This is not unique to
the patch, it how nested composites are handled in general. That is
if you passed in ROW(ROW()), the first ROW would be a hash while the
2nd row would be a scalar.
On the other end, the same is true when returning. If you try to
return a nested composite or array, the child better be a string as it
wont let you pass a hash:
= create type foo as (a int[]);
= create or replace function foo_in(foo) returns foo as $$ shift; $$
language plperl;
= create or replace function foo_out() returns foo as $$ return
{'a'=[1,2,3]}; $$ language plperl;
= -- this works with the patch because we treat the reference as a string
= select foo_in('({1,2,3})'::foo);
foo_in
-
({1,2,3})
= select foo_out();
ERROR: array value must start with { or dimension information
CONTEXT: PL/Perl function foo_out
STATEMENT: SELECT foo_out();
-- also works as a straight string
= create or replace function foo_out_str() returns foo as $$ return
{'a'='{1,2,3}'}; $$ language plperl;
= select foo_out_str();
foo_out_str
-
({1,2,3})
(1 row)
Anyone object to fixing the above as part of this patch? That is
making plperl_(build_tuple_result, modify_tuple, return_next,
hash_from_tuple) handle array and hash (composite/row) types
consistently? And _that_ would be to recurse and turn them from/into
perl objects. Thoughts?
--
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] exposing COPY API
On Fri, Feb 4, 2011 at 09:48, Andrew Dunstan and...@dunslane.net wrote: I'd like to be able to add a callback function to construct the values for the tuple. So it would become something like: typedef void (*copy_make_values) (CopyState cstate, NumFieldsRead int); You can do nothing interesting in the callback probably because the details of CopyState is not exported yet. Also, we should pass through user context for such kind of callback. The prototype of would have void *userdata. Of course, I want this so I could construct a text array from the read in data, but I could also imagine a foreign data wrapper wanting to mangle the data before handing it to postgres, say by filling in a field or hashing it. Could you explain the actual use-cases and examples? I think we need to have SQL-level extensibility if we provide such flexibility. I guess typical users don't want to write functions with C for each kind of input files. Note that pg_bulkload has a similar feature like as: CREATE FUNCTION my_function(...) RETURNS record AS ...; COPY tbl FROM 'file' WITH (make_record_from_line = my_function) -- Itagaki Takahiro -- 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] exposing COPY API
Of course, I want this so I could construct a text array from the read in data, but I could also imagine a foreign data wrapper wanting to mangle the data before handing it to postgres, say by filling in a field or hashing it. Could you explain the actual use-cases and examples? I think we need to have SQL-level extensibility if we provide such flexibility. I guess typical users don't want to write functions with C for each kind of input files. Note that pg_bulkload has a similar feature like as: CREATE FUNCTION my_function(...) RETURNS record AS ...; COPY tbl FROM 'file' WITH (make_record_from_line = my_function) Umm, where? I can't find this in the documentation http://pgbulkload.projects.postgresql.org/pg_bulkload.html nor in the source code. And how would module like that provide an extra copy option? The object, as I have explained previously, is to have a FDW that returns a text array from a (possibly irregularly shaped) file. So, given this file: 1,,2,3 4,5,6 select t[4] as a,t[2] as b from my_fdw_table; would return a | b - 3 | | 5 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] keeping a timestamp of the last stats reset (for a db, table and function)
Thinking I should start with why I think this patch is neat...most of
the servers I deal with are up 24x7 minus small amounts of downtime,
presuming everyone does their job right that is. In that environment,
having a starting timestamp for when the last stats reset happened lets
you quickly compute some figures in per-second terms that are pretty
close to actual average activity on the server. Some examples of how I
would use this:
psql -c
SELECT
CAST(buffers_backend * block_size AS numeric) / seconds_uptime /
(1024*1024)
AS backend_mb_per_sec
FROM
(SELECT *,extract(epoch from now()-stats_reset) AS seconds_uptime,
(SELECT cast(current_setting('block_size') AS int8)) AS block_size
FROM pg_stat_bgwriter) AS raw WHERE raw.seconds_uptime 0
backend_mb_per_sec
4.27150807681618
psql -c
SELECT
datname,CAST(xact_commit AS numeric) / seconds_uptime
AS commits_per_sec
FROM
(SELECT *,extract(epoch from now()-stats_reset) AS seconds_uptime
FROM pg_stat_database) AS raw WHERE raw.seconds_uptime 0
datname | commits_per_sec
---+
template1 | 0.0338722604313051
postgres | 0.0363144438470267
gsmith| 0.0820573653236174
pgbench | 0.059147072347085
Now I reset, put some load on the system and check the same stats
afterward; watch how close these match up:
$ psql -d pgbench -c select pg_stat_reset()
$ pgbench -j 4 -c 32 -T 30 pgbench
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: simple
number of clients: 32
number of threads: 4
duration: 30 s
number of transactions actually processed: 6604
tps = 207.185627 (including connections establishing)
tps = 207.315043 (excluding connections establishing)
datname | commits_per_sec
---+
pgbench | 183.906308135572
Both these examples work as I expected, and some playing around with the
patch didn't find any serious problems with the logic it implements.
One issue though, an oversight I think can be improved upon; watch what
happens when I create a new database:
$ createdb blank
$ psql -c select datname,stats_reset from pg_stat_database where
datname='blank'
datname | stats_reset
-+-
blank |
That's not really what I would hope for here. One major sort of
situation I'd like this feature to work against is the one where someone
asks for help but has never touched their database stats before, which
is exactly what I'm simulating here. In this case that person would be
out of luck, the opposite of the experience I'd like a newbie to have at
this point.
The logic Tomas put in here to initialize things in the face of never
having a stat reset is reasonable. But I think to really be complete,
this needs to hook database creation and make sure the value gets
initialized with the current timestamp, not just be blank. Do that, and
I think this will make a nice incremental feature on top of the existing
stats structure.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books
--
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] exposing COPY API
On Fri, Feb 4, 2011 at 11:32, Andrew Dunstan and...@dunslane.net wrote: Umm, where? I can't find this in the documentation http://pgbulkload.projects.postgresql.org/pg_bulkload.html Here: http://pgbulkload.projects.postgresql.org/pg_bulkload.html#filter The object, as I have explained previously, is to have a FDW that returns a text array from a (possibly irregularly shaped) file. I remember the text array proposal, but if the extension is written in C, it can only handle one kind of input files. If another file is broken in a different way, you need to rewrite the C code, no? -- Itagaki Takahiro -- 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] exposing COPY API
On 02/03/2011 09:43 PM, Itagaki Takahiro wrote: On Fri, Feb 4, 2011 at 11:32, Andrew Dunstanand...@dunslane.net wrote: Umm, where? I can't find this in the documentation http://pgbulkload.projects.postgresql.org/pg_bulkload.html Here: http://pgbulkload.projects.postgresql.org/pg_bulkload.html#filter The object, as I have explained previously, is to have a FDW that returns a text array from a (possibly irregularly shaped) file. I remember the text array proposal, but if the extension is written in C, it can only handle one kind of input files. If another file is broken in a different way, you need to rewrite the C code, no? AFAICT, this doesn't support ragged tables with too many columns, which is my prime use case. If it supported variadic arguments in filter functions it might come closer. But where does the COPY syntax you showed come in? Also, a FDW allows the COPY to be used as a FROM target, giving it great flexibility. AFAICT this does not. 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] [PERFORM] pgbench to the MAXINT
Robert Haas wrote: At least in my book, we need to get this committed in the next two weeks, or wait for 9.2. Yes, I was just suggesting that I was not going to get started in the first week or two given the other pgbench related tests I had queued up already. Those are closing up nicely, and I'll start testing performance of this change over the weekend. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books -- 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] autogenerating error code lists (was Re: [COMMITTERS] pgsql: Add foreign data wrapper error code values for SQL/MED.)
On Sun, Jan 30, 2011 at 6:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: Just in a quick read-through of the patches, the only things I noticed Oh, a third thing: the patch places errcodes.txt under src/include, which does not seem even approximately right. src/backend/utils seems like a saner place for it. All right, committed after fixing merge conflicts and, I hope, taking sensible actions based on your comments. Oh, shoot, I forgot to add the copyright notice to errcodes.txt. Let me fix that... -- 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] Visual Studio 2010/Windows SDK 7.1 support
On Sun, Jan 30, 2011 at 3:26 PM, Magnus Hagander mag...@hagander.net wrote: Magnus, are you planning to get this committed for 9.1? I'd like to, but I'm not sure I'll have the time. Per the comment from Brar it was intended as an initial attempt not quite ready yet, so it's not something we should hold up the CF / release for. OK, I'm marking it Returned with Feedback. We can resurrect it if need be. -- 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] exposing COPY API
On Fri, Feb 4, 2011 at 12:17, Andrew Dunstan and...@dunslane.net wrote: http://pgbulkload.projects.postgresql.org/pg_bulkload.html#filter AFAICT, this doesn't support ragged tables with too many columns, which is my prime use case. If it supported variadic arguments in filter functions it might come closer. It will be good improvement for pg_bulkload, but it's off-topic ;-) Also, a FDW allows the COPY to be used as a FROM target, giving it great flexibility. AFAICT this does not. BTW, which do you want to improve, FDW or COPY FROM? If FDW, the better API would be raw version of NextCopyFrom(). For example: bool NextRawFields(CopyState cstate, char **fields, int *nfields) The caller FDW has responsibility to form tuples from the raw fields. If you need to customize how to form the tuples from various fields, the FDW also need to have such extensibility. If COPY FROM, we should implement all the features in copy.c rather than exported APIs. -- Itagaki Takahiro -- 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] We need to log aborted autovacuums
On Sun, Jan 30, 2011 at 10:26 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jan 30, 2011 at 10:03 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of dom ene 30 23:37:51 -0300 2011: Unless I'm missing something, making autovacuum.c call ConditionalLockRelationOid() is not going to work, because the vacuum transaction isn't started until we get all the way down to vacuum_rel(). Maybe we need ConditionalLockRelationOidForSession or something like that? That'd be another way to go, if there are objections to what I've implemented here. Seeing as how there seem to be neither objections nor endorsements, I'm inclined to commit what I proposed more or less as-is. There remains the issue of what do about the log spam. Josh Berkus suggested logging it when log_autovacuum_min_duration != -1, which seems like a bit of an abuse of that setting, but it's certainly not worth adding another setting for, and the alternative of logging it at, say, DEBUG2 seems unappealing because you'll then have to turn on logging for a lot of unrelated crap to get this information. So on balance I think that proposal is perhaps the least of evils. -- 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] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
On Fri, Jan 14, 2011 at 6:15 AM, Simon Riggs si...@2ndquadrant.com wrote: Patch to implement the proposed feature attached, for CFJan2011. 2 sub-command changes: ALTER TABLE foo ADD FOREIGN KEY fkoo ... NOT VALID; ALTER TABLE foo VALIDATE CONSTRAINT fkoo; This patch, which seems to be the latest version, no longer applies, and has not been updated based on the previous provided review comments. Also, this diff hunk looks scary to me: + if (must_use_query) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), +errmsg(cannot SELECT from primary key of relation \%s\, + RelationGetRelationName(rel; + What's the justification for that? -- 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
[HACKERS] Compilation failed
Hi, When I run git pull from the git master and make, I encountered the following compilation error. gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -g -I../../src/port -I../../src/include -D_GNU_SOURCE -c chklocale.c -o chklocale_srv.o In file included from ../../src/include/postgres.h:48, from chklocale.c:17: ../../src/include/utils/elog.h:69:28: error: utils/errcodes.h: No such file or directory make[2]: *** [chklocale_srv.o] Error 1 make[2]: Leaving directory `/home/postgres/head/src/port' make[1]: *** [install-port-recurse] Error 2 make[1]: Leaving directory `/home/postgres/head/src' make: *** [install-src-recurse] Error 2 I guess the following commit causes a problem. - Avoid maintaining three separate copies of the error codes list. src/pl/plpgsql/src/plerrcodes.h, src/include/utils/errcodes.h, and a big chunk of errcodes.sgml are now automatically generated from a single file, src/backend/utils/errcodes.txt. - Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Compilation failed
On Thu, Feb 3, 2011 at 11:02 PM, Fujii Masao masao.fu...@gmail.com wrote: Hi, When I run git pull from the git master and make, I encountered the following compilation error. gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -g -I../../src/port -I../../src/include -D_GNU_SOURCE -c chklocale.c -o chklocale_srv.o In file included from ../../src/include/postgres.h:48, from chklocale.c:17: ../../src/include/utils/elog.h:69:28: error: utils/errcodes.h: No such file or directory make[2]: *** [chklocale_srv.o] Error 1 make[2]: Leaving directory `/home/postgres/head/src/port' make[1]: *** [install-port-recurse] Error 2 make[1]: Leaving directory `/home/postgres/head/src' make: *** [install-src-recurse] Error 2 I guess the following commit causes a problem. - Avoid maintaining three separate copies of the error codes list. src/pl/plpgsql/src/plerrcodes.h, src/include/utils/errcodes.h, and a big chunk of errcodes.sgml are now automatically generated from a single file, src/backend/utils/errcodes.txt. - The build farm doesn't look too happy with it either, but of course it worked for me here. I guess there's a missing dependency somewhere. *goes off to look* -- 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] pg_dump directory archive format / parallel pg_dump
On Wed, Feb 2, 2011 at 13:32, Joachim Wieland j...@mcknight.de wrote: Here is a rebased version with some minor changes as well. I read the patch works as below. Am I understanding correctly? 1. Open all connections in a parent process. 2. Start transactions for each connection in the parent. 3. Spawn child processes with fork(). 4. Each child process uses one of the inherited connections. I think we have 2 important technical issues here: * The consistency is not perfect. Each transaction is started with small delays in step 1, but we cannot guarantee no other transaction between them. * Can we inherit connections to child processes with fork() ? Moreover, we also need to pass running transactions to children. I wonder libpq is designed for such usage. To solve both issues, we might want a way to control visibility in a database server instead of client programs. Don't we need server-side support like [1] before developing parallel dump? [1] http://wiki.postgresql.org/wiki/ClusterFeatures#Export_snapshots_to_other_sessions I haven't tested it on Windows now but will do so as soon as the Unix part has been reviewed. It might be better to remove Windows-specific codes from the first try. I doubt Windows message queue is the best API in such console-based application. I hope we could use the same implementation for all platforms for inter-process/thread communication. -- Itagaki Takahiro -- 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] Compilation failed
On Thu, Feb 3, 2011 at 11:42 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Feb 3, 2011 at 11:02 PM, Fujii Masao masao.fu...@gmail.com wrote: Hi, When I run git pull from the git master and make, I encountered the following compilation error. gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -g -I../../src/port -I../../src/include -D_GNU_SOURCE -c chklocale.c -o chklocale_srv.o In file included from ../../src/include/postgres.h:48, from chklocale.c:17: ../../src/include/utils/elog.h:69:28: error: utils/errcodes.h: No such file or directory make[2]: *** [chklocale_srv.o] Error 1 make[2]: Leaving directory `/home/postgres/head/src/port' make[1]: *** [install-port-recurse] Error 2 make[1]: Leaving directory `/home/postgres/head/src' make: *** [install-src-recurse] Error 2 I guess the following commit causes a problem. - Avoid maintaining three separate copies of the error codes list. src/pl/plpgsql/src/plerrcodes.h, src/include/utils/errcodes.h, and a big chunk of errcodes.sgml are now automatically generated from a single file, src/backend/utils/errcodes.txt. - The build farm doesn't look too happy with it either, but of course it worked for me here. I guess there's a missing dependency somewhere. *goes off to look* I just pushed some fixes to unbreak the VPATH build (I hope) but I don't see exactly what's going wrong to cause you the problem shown above. Can you try with a completely fresh tree and send the whole build log if it's still failing? -- 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] Compilation failed
On Fri, Feb 4, 2011 at 12:11 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Feb 3, 2011 at 11:42 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Feb 3, 2011 at 11:02 PM, Fujii Masao masao.fu...@gmail.com wrote: Hi, When I run git pull from the git master and make, I encountered the following compilation error. gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -g -I../../src/port -I../../src/include -D_GNU_SOURCE -c chklocale.c -o chklocale_srv.o In file included from ../../src/include/postgres.h:48, from chklocale.c:17: ../../src/include/utils/elog.h:69:28: error: utils/errcodes.h: No such file or directory make[2]: *** [chklocale_srv.o] Error 1 make[2]: Leaving directory `/home/postgres/head/src/port' make[1]: *** [install-port-recurse] Error 2 make[1]: Leaving directory `/home/postgres/head/src' make: *** [install-src-recurse] Error 2 I guess the following commit causes a problem. - Avoid maintaining three separate copies of the error codes list. src/pl/plpgsql/src/plerrcodes.h, src/include/utils/errcodes.h, and a big chunk of errcodes.sgml are now automatically generated from a single file, src/backend/utils/errcodes.txt. - The build farm doesn't look too happy with it either, but of course it worked for me here. I guess there's a missing dependency somewhere. *goes off to look* I just pushed some fixes to unbreak the VPATH build (I hope) but I don't see exactly what's going wrong to cause you the problem shown above. Can you try with a completely fresh tree and send the whole build log if it's still failing? MSVC isn't happy with this either: Writing fmgroids.h Writing fmgrtab.c Generating probes.h... Generating errcodes.h... The filename, directory name, or volume label syntax is incorrect. Could not open src\backend\utils\errcodes.h at src/tools/msvc/Mkvcbuild.pm line 463 I can't immediately grok what I need to do to fix that. -- 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] Add ENCODING option to COPY
On Tue, Feb 1, 2011 at 13:08, Hitoshi Harada umi.tan...@gmail.com wrote: The third patch is attached, modifying mb routines so that they can receive conversion procedures as FmgrInof * and save the function pointer in CopyState. I tested it with encoding option and could not see performance slowdown. Hmm, sorry, the patch was wrong. Correct version is attached. Here is a brief review for the patch. * Syntax: ENCODING encoding vs. ENCODING 'encoding' We don't have to quote encoding names in the patch, but we always need quotes for CREATE DATABASE WITH ENCODING. I think we should modify CREATE DATABASE to accept unquoted encoding names aside from the patch. Changes in pg_wchar.h are the most debatable parts of the patch. The patch adds pg_cached_encoding_conversion(). We normally use pg_do_encoding_conversion(), but it is slower than the proposed function because it lookups conversion proc from catalog every call. * Can we use #ifndef FRONTEND in the header? Usage of fmgr.h members will broke client applications without the #ifdef, but I guess client apps don't always have definitions of FRONTEND. If we don't want to change pg_wchar.h, pg_conversion_fn.h might be a better header for the new API because FindDefaultConversion() is in it. Changes in copy.c looks good except a few trivial cosmetic issues: * encoding_option could be a local variable instead of cstate's member. * cstate-client_encoding is renamed to target_encoding, but I prefer file_encoding or remote_encoding. * CopyState can have conv_proc entity as a member instead of the pointer. * need_transcoding checks could be replaced with conv_proc IS NULL check. -- Itagaki Takahiro -- 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] exposing COPY API
On 02/03/2011 10:43 PM, Itagaki Takahiro wrote: Also, a FDW allows the COPY to be used as a FROM target, giving it great flexibility. AFAICT this does not. BTW, which do you want to improve, FDW or COPY FROM? If FDW, the better API would be raw version of NextCopyFrom(). For example: bool NextRawFields(CopyState cstate, char **fields, int *nfields) The caller FDW has responsibility to form tuples from the raw fields. If you need to customize how to form the tuples from various fields, the FDW also need to have such extensibility. If COPY FROM, we should implement all the features in copy.c rather than exported APIs. The problem with COPY FROM is that nobody's come up with a good syntax for allowing it as a FROM target. Doing what I want via FDW neatly gets us around that problem. But I'm quite OK with doing the hard work inside the COPY code - that's what my working prototype does in fact. One thing I'd like is to to have file_fdw do something we can't do another way. currently it doesn't, so it's nice but uninteresting. 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
