Re: [HACKERS] Elementary dependency look-up

[Greg Smith]

On Wed, 9 Sep 2009, decibel wrote:

What might be more useful is a view that takes the guesswork out of using 
pg_depend. Namely, convert (ref)classid into a catalog table name (or better 
yet, what type of object it is), (ref)objid into an actual object name, and 
(ref)objsubid into a real name.


Here's V1 of a depend unraveler I needed recently and that's saved me a 
bunch of time:


SELECT
  c1.oid as relid,
  n1.nspname || '.' || c1.relname as relation,
  c1.relkind,
  CASE
WHEN c1.relkind='r' THEN 'table'
WHEN c1.relkind='i' THEN 'index'
WHEN c1.relkind='S' THEN 'sequence'
WHEN c1.relkind='v' THEN 'view'
WHEN c1.relkind='c' THEN 'composite'
WHEN c1.relkind='t' THEN 'TOAST'
ELSE '?'
  END as "kind",
  c2.oid as relid,
  n2.nspname || '.' || c2.relname as dependency,
  c2.relkind,
  CASE
WHEN c2.relkind='r' THEN 'table'
WHEN c2.relkind='i' THEN 'index'
WHEN c2.relkind='S' THEN 'sequence'
WHEN c2.relkind='v' THEN 'view'
WHEN c2.relkind='c' THEN 'composite'
WHEN c2.relkind='t' THEN 'TOAST'
ELSE '?'
  END as "kind"
FROM
  pg_depend d,
  pg_class c1,
  pg_namespace n1,
  pg_class c2,
  pg_namespace n2
WHERE
  d.objid = c1.oid AND
  c1.relnamespace = n1.oid AND
  n1.nspname NOT IN('information_schema', 'pg_catalog') AND
  n1.nspname !~ '^pg_toast' AND

  d.refobjid = c2.oid AND
  c2.relnamespace = n2.oid AND
  n2.nspname NOT IN('information_schema', 'pg_catalog') AND
  n2.nspname !~ '^pg_toast' AND

  c1.oid != c2.oid

GROUP BY n1.nspname,c1.relname,c1.oid,c1.relkind,
 n2.nspname,c2.relname,c2.oid,c2.relkind
ORDER BY n1.nspname,c1.relname;

I could throw this on the Wiki as a code snippet if anyone else wanted to 
tinker with it.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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] Why does LOG have higher priority than ERROR and WARNING?

[Peter Eisentraut]

On Mon, 2009-09-14 at 09:16 +0900, Itagaki Takahiro wrote:
> Another matter is that we use LOG level both cases of important
> activity logging and mere performance or query logging. Maybe
> we should have used another log level (PERFORMANCE?) for the
> latter case, and its priority is less than WARNINGs and LOGs.

Ideally, LOG messages are messages that you explicitly requested using
various log_* parameters.  If you need more control, we could
conceivably add more of those.


-- 
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] RfD: more powerful "any" types

[Pavel Stehule]

2009/9/13 Hannu Krosing :
> On Sun, 2009-09-13 at 21:50 +0200, Pavel Stehule wrote:
>> Hello
>>
>> >
>> > ANY [TYPE] and SAME AS [TYPE OF] are syntactic sugar indeed, but they
>> > are much more SQL-like than needing to write "any" or anyelement(n) as
>> > argument type or return type
>> >
>>
>> I looked on possibilities in gram.y and I thing, type identifiers
>>
>> "ANY TYPE" is possible without any problems (this should be synonym for 
>> "any"),
>> "SAME AS" needs add "same" keyword to col_name_keywords , i.e. "same"
>> is prohibited for function names - it should be a problem

I afraid so this technique isn't allowed in SQL parser, or is i

>>
>> regards
>> Pavel Stehule
>>
>> I found so pgparser provide some ref type syntax via % symbol. So we
>> can use following syntax:
>>
>> CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b a%TYPE)
>> RETURNS a%TYPE ...
>>
>> It is not pretty like SAME AS, but I am sure, so this is doable
>> (parser knows it now)
>>
>> any other ideas?
>
> Hmm, maybe try to make lexer recognize "SAME AS" as one token and then
> deal with other cases of 'name AS' ?

I afraid so this technique isn't allowed in SQL parser, or is it?

>
> Or make the syntax a little uglier,
>
> CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b TYPE OF a)
>
> CREATE OR REPLACE FUNCTION foo(ANY TYPE, TYPE OF $1)

TYPE OF generate shift/reduce too :(

Pavel

>
>
> and maybe try
>
> CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b TYPE OF a)
> RETURNS ARRAY OF TYPE OF a
>
> instead of
>
> CREATE OR REPLACE FUNCTION foo(a anyelement, b anyelement)
> RETURNS anyarray
>
>>
>> regards
>> Pavel Stehule
>
>
> --
> Hannu Krosing   http://www.2ndQuadrant.com
> PostgreSQL Scalability and Availability
>   Services, Consulting and Training
>
>
>

-- 
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] [PATCH] 8.5 plpgsql change for named notation: treat word following AS keyword as label v3

[Pavel Stehule]

2009/9/14 Tom Lane :
> Robert Haas  writes:
>> So, I guess I'm sadly left feeling that we should probably reject this
>> patch.  Anyone want to argue otherwise?
>
> +1.  I'm really hoping to get something done about the plpgsql parsing
> situation before 8.5 is out, so this should be a dead end anyway.
>

I have a WIP patch for integration main SQL parser to plpgsql. I'll
send it to this weekend.

regards
Pavel Stehule

>                        regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RfD: more powerful "any" types

[Pavel Stehule]

2009/9/13 decibel :
> On Sep 12, 2009, at 5:54 PM, Andrew Dunstan wrote:
>>
>> decibel wrote:
>>>
>>> Speaking of concatenation...
>>>
>>> Something I find sorely missing in plpgsql is the ability to put
>>> variables inside of a string, ie:
>>>
>>> DECLARE
>>> v_table text := ...
>>> v_sql text;
>>> BEGIN
>>> v_sql := "SELECT * FROM $v_table";
>>>
>>> Of course, I'm assuming that if it was easy to do that it would be done
>>> already... but I thought I'd just throw it out there.
>>>
>>
>> Then use a language that supports variable interpolation in strings, like
>> plperl, plpythonu, plruby  instead of plpgsql.
>
>
> Which makes executing SQL much, much harder.
>
> At least if we get sprintf dealing with strings might become a bit easier...

This feature is nice - but very dangerous - it the most easy way how
do vulnerable (on SQL injection) application!

regards
Pavel Stehule

> --
> Decibel!, aka Jim C. Nasby, Database Architect  deci...@decibel.org
> Give your computer some brain candy! www.distributed.net Team #1828
>
>
>

-- 
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] Issues for named/mixed function notation patch

[Pavel Stehule]

2009/9/14 Robert Haas :
> On Mon, Aug 24, 2009 at 3:19 PM, Pavel Stehule  
> wrote:
>> I reworked patch to respect mentioned issues. - this patch still
>> implement mixed notation - I am thing so this notation is really
>> important. All others I respect. The behave is without change, fixed
>> some bugs, enhanced regress tests.
>
> This does not compile.

I'll recheck it today

Pavel

>
> ...Robert
>

-- 
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] clang's static checker report.

[Tom Lane]

Grzegorz Jaskiewicz  writes:
> usual round of updates to the scan report.
> Today's report available at:

> http://zlew.org/postgresql_static_check/scan-build-2009-09-12-1/

Looks like the clang guys still have some work to do.  The
null-dereference reports, in particular, seem to be willing to make
self-contradictory assumptions in order to claim there is a possibility
of a null dereference.  The clearest example I found was this one:
http://zlew.org/postgresql_static_check/scan-build-2009-09-12-1/report-Ybdv3J.html#EndPath
where to conclude that lp might be null, clang first assumes
PageGetMaxOffsetNumber(page) < offnum (at line 4251); but it then
must assume that that is *false* in order to suppose that control
can arrive at the dereference inside ItemIdIsNormal at line 4254.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Timestamp to time_t

[Scott Mohekey]

Is it possible to convert from a Timestamp to time_t ? I see functions
mentioned in utils/timestamp.h for converting between TimestampTz and
time_t, but nothing for Timestamp.
What is the relationship between Timestamp and TimestampTz?

Scott Mohekey
Systems/Application Specialist – OnTrack – Telogis, Inc.
www.telogis.com  www.telogis.co.nz
+1 949 625-4115 ext. 207 (USA)  +64 3339 2825 x207 (NZ)

Leading Global Platform for Location Based Services
--
This e-mail, and any attachments, is intended only for use by the
addressee(s) named herein and may contain legally privileged and/or
confidential information.  It is the property of Telogis.  If you are not
the intended recipient of this e-mail, you are hereby notified that any
dissemination, distribution or copying of this e-mail, any attachments
thereto, and use of the information contained, is strictly prohibited.  If
you have received this e-mail in error, please notify the sender and
permanently delete the original and any copy there of.

Re: [HACKERS] logging hook for database audit

[Itagaki Takahiro]

Magnus Hagander  wrote:

> I have been working with josh tolley that changes some infrastructure  
> around this, with one of the future goals to be able to implement just  
> this kind of features. I need to merge some changes from josh, and  
> will then try to post a wip patch as soon as possible. Please look at  
> this one before you start working on this - probably theoverlap will  
> be very large.

Thanks, it will be helpful.

At the moment, my idea is adding special sqlcodes for some LOG messages.

ERRCODE_SUCCESSFUL_AUTOVACUUM  = "1"
ERRCODE_SUCCESSFUL_CHECKPOINTS = "2"
...
ereport(LOG, errcode(ERRCODE_SUCCESSFUL_AUTOVACUUM),
 errmsg("autovacuum messages")));

New GUC parameters xxx_category is a list of categories.
Only logs listed here is written for each log. The default
values are '*' (all categories). Messages without sqlcodes
are put into "others" category.

{stderr|csvlog|syslog}_category = [ * | autovacuum | ... | others ]

This approach is less impact to the codes and just utilizes unused
sqlcodes of LOG or lower level messages. However, it has some limitations:

  * We cannot categorize ERROR or higher level messages
because they've used sqlcodes as original purposes.
  * The number of categories are restricted to 30 or so, because category
filtera are managed with bits32. Categorized messages should use
sqlcodes between "1" and "N".
  * We need to add sqlcodes for each LOGs. It would be hard work, but
needed anyway if we will support any kinds of per-category log filters.

Comments welcome.

Regards,
---
ITAGAKI Takahiro
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] Rough draft: easier translation of psql help

[Alvaro Herrera]

Peter Eisentraut wrote:

> Instead of translating the whole string, that is (picking a shorter
> example)
> 
> N_("ALTER TEXT SEARCH PARSER name RENAME TO newname")
> 
> we really only want to translate the placeholders, so it could look like
> this:
> 
>appendPQExpBuffer(buf,
>   "ALTER TEXT SEARCH PARSER %s RENAME TO %s",
>   _("name"),
>   _("newname"));

+1000

Should create_help.pl be run on "make dist"?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] XLogInsert

[Jeff Janes]

On Wed, Aug 19, 2009 at 9:49 AM, Tom Lane  wrote:

> Jeff Janes  writes:
> > If I read the code correctly, the only thing that is irrevocable is
> > that it writes into
> > rdt->next, and if it saved an old copy of rdt first, then it could
> > revoke the changes just
> > by doing rdt_old->next=NULL.  If that were done, then I think this
> > code could be
> > moved out of the section holding the WALInsertLock.
>
> Hmm, I recall that the changes are ... or were ... more complex.
> The tricky case I think is where we have to go back and redo the
> block-backup decisions after discovering that the checkpoint REDO
> pointer has just moved.
>
> If you can get the work out of the WALInsertLock section for just a
> few more instructions, it would definitely be worth doing.
>

I've attached a patch which removes the iteration over the blocks to be
backed-up from the critical section of XLogInsert.  Now those blocks are
only looped over in one piece of code which both computes the CRC and builds
the linked list, rather than having parallel loops.

I've used an elog statement (not shown in patch) to demonstrate that the
"goto begin;" after detecting REDO race actually does get executed under a
standard workload, (pgbench -c10).  Two to 4 out of 10 the backends execute
that code path for each checkpoint on my single CPU machine.  By doing a
kill -9 on a process, to simulate a crash, during the period after the goto
begin is execercised but before the precipitating heckpoint completes, I can
force it to use the written WAL records in recovery.  The database
automatically recovers and the results are self-consistent.

I cannot imagine any other races, rare events, or action at a distance that
could come into play with this code change, so I cannot think of anything
else to test at the moment.

I could not detect a speed difference with pgbench, but as I cannot get
pgbench to be XLogInsert bound, that is not surprising.  Using the only
XLogInsert-bound test case I know of, parallel COPY into a skinny, unindexed
table, using 8 parallel copies on a 4 x dual-core x86_64 and with fsync
turned off (to approxiamately simulate SSD, which I do not have), I get a
speed improvement of 2-4% with the patch over unpatched head.  Maybe with
more CPUs the benefit would be greater.

That small improvement is probably not very attractive, however I think the
patch makes the overall code a bit cleaner, so it may be warranted on that
ground.  Indeed, my motivation for working on this is that I kept beating my
head against the complexity of the old code, and thought that simplifying it
would make future work easier.

Cheers,

Jeff
Index: xlog.c
===
RCS file: /home/jjanes/pgrepo/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.352
diff -c -r1.352 xlog.c
*** xlog.c	10 Sep 2009 09:42:10 -	1.352
--- xlog.c	10 Sep 2009 19:27:08 -
***
*** 540,548 
  	bool		dtbuf_bkp[XLR_MAX_BKP_BLOCKS];
  	BkpBlock	dtbuf_xlg[XLR_MAX_BKP_BLOCKS];
  	XLogRecPtr	dtbuf_lsn[XLR_MAX_BKP_BLOCKS];
! 	XLogRecData dtbuf_rdt1[XLR_MAX_BKP_BLOCKS];
! 	XLogRecData dtbuf_rdt2[XLR_MAX_BKP_BLOCKS];
! 	XLogRecData dtbuf_rdt3[XLR_MAX_BKP_BLOCKS];
  	pg_crc32	rdata_crc;
  	uint32		len,
  write_len;
--- 540,550 
  	bool		dtbuf_bkp[XLR_MAX_BKP_BLOCKS];
  	BkpBlock	dtbuf_xlg[XLR_MAX_BKP_BLOCKS];
  	XLogRecPtr	dtbuf_lsn[XLR_MAX_BKP_BLOCKS];
! 	XLogRecData dtbuf_rdt1[XLR_MAX_BKP_BLOCKS];	/*xlog header of backed up block*/
! 	XLogRecData dtbuf_rdt2[XLR_MAX_BKP_BLOCKS];	/*part of block before the hole*/
! 	XLogRecData dtbuf_rdt3[XLR_MAX_BKP_BLOCKS];	/*part of block after the hole*/
! 	XLogRecData dummy_node;	/* head node for back-up block chain*/
! 	XLogRecData *rdt2;	/* tail pointer for back-up block chain*/
  	pg_crc32	rdata_crc;
  	uint32		len,
  write_len;
***
*** 663,696 
  
  	/*
  	 * Now add the backup block headers and data into the CRC
  	 */
  	for (i = 0; i < XLR_MAX_BKP_BLOCKS; i++)
  	{
! 		if (dtbuf_bkp[i])
! 		{
! 			BkpBlock   *bkpb = &(dtbuf_xlg[i]);
! 			char	   *page;
  
! 			COMP_CRC32(rdata_crc,
! 	   (char *) bkpb,
! 	   sizeof(BkpBlock));
! 			page = (char *) BufferGetBlock(dtbuf[i]);
! 			if (bkpb->hole_length == 0)
! 			{
! COMP_CRC32(rdata_crc,
! 		   page,
! 		   BLCKSZ);
! 			}
! 			else
! 			{
! /* must skip the hole */
! COMP_CRC32(rdata_crc,
! 		   page,
! 		   bkpb->hole_offset);
  COMP_CRC32(rdata_crc,
! 		   page + (bkpb->hole_offset + bkpb->hole_length),
! 		   BLCKSZ - (bkpb->hole_offset + bkpb->hole_length));
! 			}
  		}
  	}
  
--- 665,740 
  
  	/*
  	 * Now add the backup block headers and data into the CRC
+ 	 * Also make a separate chain of entries for the backup blocks.  
+ 	 * Once we know we do not need to repeat the process due to races,
+ 	 * the two chains are stitched together so that we  don't need 
+ 	 * to special-case them in the write loop.  At the exit o

[HACKERS] test_fsync file overrun

[Jeff Janes]

test_fsync in tools/fsync pre-creates a 16MB file.  If it is given a number
of iterations greater than 1024 (like one might use if trying to see what
happens when NVRAM gets filled, or on a journaling file system), than one of
the writes being timed will have to extend the size of the pre-created test
file, which can greatly skew the results.

This patch uses lseek to periodically restart at the beginning of the file,
rather than writing past the end of it.

Cheers,

Jeff
Index: test_fsync.c
===
RCS file: /home/jjanes/pgrepo/pgsql/src/tools/fsync/test_fsync.c,v
retrieving revision 1.24
diff -c -r1.24 test_fsync.c
*** test_fsync.c	10 Aug 2009 18:19:06 -	1.24
--- test_fsync.c	13 Sep 2009 17:29:27 -
***
*** 31,36 
--- 31,37 
  #endif
  
  #define WRITE_SIZE	(16 * 1024) /* 16k */
+ #define REWIND_FILE	if (i%1020 == 1019 && (-1 == lseek(tmpfile,0,SEEK_SET))) die ("Cannot lseek.");
  
  void		die(char *str);
  void		print_elapse(struct timeval start_t, struct timeval elapse_t);
***
*** 148,156 
  	if ((tmpfile = open(filename, O_RDWR | OPEN_SYNC_FLAG, 0)) == -1)
  		die("Cannot open output file.");
  	gettimeofday(&start_t, NULL);
! 	for (i = 0; i < loops; i++)
  		if (write(tmpfile, buf, WRITE_SIZE) != WRITE_SIZE)
  			die("write failed");
  	gettimeofday(&elapse_t, NULL);
  	close(tmpfile);
  	printf("\tone 16k o_sync write   ");
--- 149,160 
  	if ((tmpfile = open(filename, O_RDWR | OPEN_SYNC_FLAG, 0)) == -1)
  		die("Cannot open output file.");
  	gettimeofday(&start_t, NULL);
! 	for (i = 0; i < loops; i++) 
! 	{
! 		REWIND_FILE;
  		if (write(tmpfile, buf, WRITE_SIZE) != WRITE_SIZE)
  			die("write failed");
+ 	};
  	gettimeofday(&elapse_t, NULL);
  	close(tmpfile);
  	printf("\tone 16k o_sync write   ");
***
*** 163,168 
--- 167,173 
  	gettimeofday(&start_t, NULL);
  	for (i = 0; i < loops; i++)
  	{
+ 		REWIND_FILE;
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)
  			die("write failed");
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)
***
*** 188,195 
--- 193,203 
  		die("Cannot open output file.");
  	gettimeofday(&start_t, NULL);
  	for (i = 0; i < loops; i++)
+ 	{
+ 		REWIND_FILE;
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)
  			die("write failed");
+ 	}
  	gettimeofday(&elapse_t, NULL);
  	close(tmpfile);
  	printf("\topen o_dsync, write");
***
*** 205,212 
--- 213,223 
  		die("Cannot open output file.");
  	gettimeofday(&start_t, NULL);
  	for (i = 0; i < loops; i++)
+ 	{
+ 		REWIND_FILE;
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)
  			die("write failed");
+ 	}
  	gettimeofday(&elapse_t, NULL);
  	close(tmpfile);
  	printf("\topen o_sync, write ");
***
*** 223,228 
--- 234,240 
  	gettimeofday(&start_t, NULL);
  	for (i = 0; i < loops; i++)
  	{
+ 		REWIND_FILE;
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)
  			die("write failed");
  		fdatasync(tmpfile);
***
*** 242,247 
--- 254,260 
  	gettimeofday(&start_t, NULL);
  	for (i = 0; i < loops; i++)
  	{
+ 		REWIND_FILE;
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)
  			die("write failed");
  		if (fsync(tmpfile) != 0)
***
*** 265,270 
--- 278,284 
  	gettimeofday(&start_t, NULL);
  	for (i = 0; i < loops; i++)
  	{
+ 		REWIND_FILE;
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)
  			die("write failed");
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)
***
*** 286,291 
--- 300,306 
  	gettimeofday(&start_t, NULL);
  	for (i = 0; i < loops; i++)
  	{
+ 		REWIND_FILE;
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)
  			die("write failed");
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)
***
*** 305,310 
--- 320,326 
  	gettimeofday(&start_t, NULL);
  	for (i = 0; i < loops; i++)
  	{
+ 		REWIND_FILE;
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)
  			die("write failed");
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)
***
*** 326,331 
--- 342,348 
  	gettimeofday(&start_t, NULL);
  	for (i = 0; i < loops; i++)
  	{
+ 		REWIND_FILE;
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)
  			die("write failed");
  		if (write(tmpfile, buf, WRITE_SIZE / 2) != WRITE_SIZE / 2)

-- 
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] Elementary dependency look-up

[Josh Williams]

On Sun, 2009-09-13 at 21:20 -0400, Robert Haas wrote:
> I'm not sure there's any point in reviewing this patch in its present
> form.  Barring objections (or a new version), I think we should mark
> this Returned with Feedback.
> 
> ...Robert

Yeah, sounds reasonable.  The new version probably won't look at all
like the current one, so no need to waste reviewer cycles on it.

I'll work on a revised version; feel free to mark it as such in the mean
time.  Thanks,

- Josh Williams



-- 
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] Patch for 8.5, transformationHook

[Robert Haas]

On Tue, Aug 11, 2009 at 12:09 AM, Pavel Stehule  wrote:
> 2009/8/10 Tom Lane :
>> Robert Haas  writes:
>>> On Sun, Jul 26, 2009 at 9:29 AM, Pavel Stehule 
>>> wrote:
 new patch add new contrib "transformations" with three modules
 anotation, decode and json.
>>
>>> These are pretty good examples, but the whole thing still feels a bit
>>> grotty to me.  The set of syntax transformations that can be performed
>>> with a hook of this type is extremely limited - in particular, it's
>>> the set of things where the parser thinks it's valid and that the
>>> structure is reasonably similar to what you have in mind, but the
>>> meaning is somewhat different.  The fact that two of your three
>>> examples require your named and mixed parameters patch seems to me to
>>> be evidence of that.
>>
>> I finally got around to looking at these examples, and I still don't
>> find them especially compelling.  Both the decode and the json example
>> could certainly be done with regular function definitions with no need
>> for this hook.  The => to AS transformation maybe not, but so what?
>> The reason we don't have that one in core is not technological.
>>
>> The really fundamental problem with this hook is that it can't do
>> anything except create syntactic sugar, and a pretty darn narrow class
>> of syntactic sugar at that.  Both the raw parse tree and the transformed
>> tree still have to be valid within the core system's understanding.
>> What's more, since there's no hook in ruleutils.c, what is going to come
>> out of the system (when dumping, examining a view, etc) is the
>> transformed expression --- so you aren't really hiding any complexity
>> from the user, you're just providing a one-time shorthand that will be
>> expanded into a notation he also has to be familiar with.
>>
>
> I agree - so this could be a problem
>
>> Now you could argue that we've partly created that restriction by
>> insisting that the hook be in transformFuncCall and not transformExpr.
>> But that only restricts the subset of raw parse trees that you can play
>> with; it doesn't change any of the other restrictions.
>>
>> Lastly, I don't think the problem of multiple hook users is as easily
>> solved as Pavel claims.  These contrib modules certainly fail to solve
>> it.  Try unloading (or re-LOADing) them in a different order than they
>> were loaded.
>>
>
> There are two possible solution
>
> a) all modules should be loaded only from configuration
> b) modules should be loaded in transformation time - transformation of
> functions should be substituted some registered function for some
> functions. This little bit change sense of this patch. But it's enough
> for use cases like DECODE, JSON, SOAP. It's mean one new column to
> pg_proc - like protransformfunc.
>
> ???
> Pavel
>
>> So on the whole I still think this is a solution looking for a problem,
>> and that any problems it could solve are better solved elsewhere.

I am in the process of looking through the patches to be assigned for
the September CommitFest, and it seems to me that we really haven't
made any progress here since the last CommitFest.  Jeff Davis provided
a fairly good summary of the issues:

http://archives.postgresql.org/message-id/1249784508.9256.892.ca...@jdavis

I don't think we really gain much by assigning yet another reviewer to
this patch.  The patch is simple enough and doesn't really need any
further code review AFAICS, but nobody except the patch author seems
confident that this is all that useful.[1] I'm biased by the fact that
I reviewed this patch and didn't particularly like it either, but I
think we need more than to think about committing this in the face of
Tom Lane's opinion (which I share, FWIW) that this is of very limited
usefulness.

...Robert

[1] Indeed, the few supportive responses were along the lines of "oh -
this should help with X" to which the response was, in at least two
cases, "well actually no it won't".

-- 
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] [PATCH] 8.5 plpgsql change for named notation: treat word following AS keyword as label v3

[Tom Lane]

Robert Haas  writes:
> So, I guess I'm sadly left feeling that we should probably reject this
> patch.  Anyone want to argue otherwise?

+1.  I'm really hoping to get something done about the plpgsql parsing
situation before 8.5 is out, so this should be a dead end anyway.

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] [PATCH] 8.5 plpgsql change for named notation: treat word following AS keyword as label v3

[Robert Haas]

On Thu, May 21, 2009 at 2:46 PM, Steve Prentice  wrote:
> On May 21, 2009, at 10:52 AM, Tom Lane wrote:
>>
>> It's probably time to bite the bullet and redo the parser as has been
>> suggested in the past, ie fix things so that the main parser is used.
>> Ideally I'd like to switch the name resolution priority to be more
>> Oracle-like, but even if we don't do that it would be a great
>> improvement to have actual syntactic knowledge behind the lookups.
>
> That kind of refactoring is beyond my experience-level with the code, but I
> can't say I disagree with your analysis.
>
>> Just for the record, you'd have to put the same kluge into the T_RECORD
>> and T_ROW cases if we wanted to do it like this.
>
> Patch updated.

I played around a bit with the latest version of this patch tonight,
but I'm replying to this previous version for the sake of being able
to quote more of the relevant discussion.

First, I applied this patch, which resulted in a successful compile,
but PL/pgsql wouldn't load.  After scratching my head for a minute, I
recalled that this was supposed to be dependent on named and mixed
notation, so I applied both patches, which resulted in a failed
compile.  Further experimentation revealed that named and mixed
notation alone also lead to a failed compile.  I replied to the
named/mixed notation thread so hopefully Pavel will fix whatever the
problem is with that patch.

However... even assuming I can get this to work at all, it seems like
it's only going to help in a pretty limited range of cases.  Since
this is just looking for occurrences of "AS", it has a chance of
working (of course I can't test at the moment) for something like
this:

select foo as bar from generate_series(1,10) foo;

...but I think it will certainly fail for something like this:

select foo bar from generate_series(1,10) foo;

As much as I'm annoyed by the stupidity of PL/pgsql in this regard
(and I really am - I use it constantly and this is a real pain in the
neck), I think it makes more sense to wait for a more comprehensive
solution.  Also, besides the fact that this doesn't (and can't) handle
all cases, as Tom points out, this would create a real possibility
that some future use of the word AS could cause breakage at a
distance.

So, I guess I'm sadly left feeling that we should probably reject this
patch.  Anyone want to argue otherwise?

...Robert

-- 
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] Issues for named/mixed function notation patch

[Robert Haas]

On Mon, Aug 24, 2009 at 3:19 PM, Pavel Stehule  wrote:
> I reworked patch to respect mentioned issues. - this patch still
> implement mixed notation - I am thing so this notation is really
> important. All others I respect. The behave is without change, fixed
> some bugs, enhanced regress tests.

This does not compile.

...Robert

-- 
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] Elementary dependency look-up

[Robert Haas]

On Thu, Sep 10, 2009 at 10:23 AM, Robert Haas  wrote:
> On Thu, Sep 10, 2009 at 12:47 AM, Josh Williams  wrote:
>> On Wed, 2009-09-09 at 11:30 -0500, decibel wrote:
>>> On Sep 9, 2009, at 8:05 AM, Peter Eisentraut wrote:
>>> > How is this better than just reading the information directly from
>>> > pg_depend?
>>>
>>> pg_depend is very difficult to use. You have to really, really know
>>> the catalogs to be able to figure it out. Part of the problem is
>>> (afaik) there's nothing that documents every kind of record/
>>> dependency you might find in there.
>>
>> Exactly - these functions were designed around making that easier for
>> the end user.  The less poking around in system catalogs a user has to
>> do the better.
>>
>> Yeah, the documentation about what can be found in pg_depend is
>> scattered at best, though then again there doesn't seem to be a whole
>> lot in there that's of much interest to end users...  Actually, apart
>> from pg_get_serial_sequence() do we have anything else that utilizes
>> dependency data to show the user information?
>>
>>> What might be more useful is a view that takes the guesswork out of
>>> using pg_depend. Namely, convert (ref)classid into a catalog table
>>> name (or better yet, what type of object it is), (ref)objid into an
>>> actual object name, and (ref)objsubid into a real name.
>>
>> Makes sense, would be much more future-proof.  It shouldn't be difficult
>> to put in some intelligence to figure out the type of object, such as
>> looking at relkind if (ref)classid = pg_class.
>>
>> It might be a little difficult to maintain, depending on what else finds
>> its way into the system catalogs later (but then, probably not much more
>> so than INFORMATION SCHEMA is.)  Would that be preferable, over a couple
>> additional functions?
>
> +1.

I'm not sure there's any point in reviewing this patch in its present
form.  Barring objections (or a new version), I think we should mark
this Returned with Feedback.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] syslog_line_prefix

[Itagaki Takahiro]

Here is a patch to add a GUC parameter "syslog_line_prefix".
It adds prefixes to syslog and eventlog. We still have
"log_line_prefix", that will be used only for stderr logs.

We have a tip that log_line_prefix is not required for syslog
in the documentation, but we'd better to have independent setttings
if we set log_destination to 'stderr, syslog'.

http://developer.postgresql.org/pgdocs/postgres/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
| Tip:  Syslog produces its own time stamp and process ID
| information, so you probably do not want to use those escapes
| if you are logging to syslog. 

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center


syslog_line_prefix-20090914.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] COPY enhancements

[Andrew Dunstan]

Tom Lane wrote:

Josh Berkus  writes:
  

It's not as if we don't have the ability to measure performance impact.
 It's reasonable to make a requirement that new options to COPY
shouldn't slow it down noticeably if those options aren't used.  And we
can test that, and even make such testing part of the patch review.



Really?  Where is your agreed-on, demonstrated-to-be-reproducible
benchmark for COPY speed?

My experience is that reliably measuring performance costs in the
percent-or-so range is *hard*.  It's only after you've added a few of
them and they start to mount up that it becomes obvious that all those
insignificant additions really did cost something.
  


Well, I strongly suspect that the cost of the patch I'm working with is 
not in the percent-or-so range, and much more likely to be in the 
tiny-fraction-of-a-percent range. The total overhead in the non-ragged 
case is one extra test per field, plus one per null field, plus two 
tests per line.


But since you raise the question I'll conduct some tests and then you 
can criticize those. Ruling out tests a priori seems a bit extreme.


The current patch is attached for information (and in case anyone else 
wants to try some testing).


cheers

andrew
Index: src/backend/commands/copy.c
===
RCS file: /cvsroot/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.316
diff -c -r1.316 copy.c
*** src/backend/commands/copy.c	29 Jul 2009 20:56:18 -	1.316
--- src/backend/commands/copy.c	13 Sep 2009 02:57:16 -
***
*** 116,121 
--- 116,122 
  	char	   *escape;			/* CSV escape char (must be 1 byte) */
  	bool	   *force_quote_flags;		/* per-column CSV FQ flags */
  	bool	   *force_notnull_flags;	/* per-column CSV FNN flags */
+ 	boolragged; /* allow ragged CSV input? */
  
  	/* these are just for error messages, see copy_in_error_callback */
  	const char *cur_relname;	/* table name for error messages */
***
*** 822,827 
--- 823,836 
  		 errmsg("conflicting or redundant options")));
  			force_notnull = (List *) defel->arg;
  		}
+ 		else if (strcmp(defel->defname, "ragged") == 0)
+ 		{
+ 			if (cstate->ragged)
+ ereport(ERROR,
+ 		(errcode(ERRCODE_SYNTAX_ERROR),
+ 		 errmsg("conflicting or redundant options")));
+ 			cstate->ragged = intVal(defel->arg);
+ 		}
  		else
  			elog(ERROR, "option \"%s\" not recognized",
   defel->defname);
***
*** 948,953 
--- 957,972 
  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
  			  errmsg("COPY force not null only available using COPY FROM")));
  
+ 	/* Check ragged */
+ 	if (!cstate->csv_mode && cstate->ragged)
+ 		ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+  errmsg("COPY ragged available only in CSV mode")));
+ 	if (cstate->ragged && !is_from)
+ 		ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ 			  errmsg("COPY  ragged only available using COPY FROM")));
+ 
  	/* Don't allow the delimiter to appear in the null string. */
  	if (strchr(cstate->null_print, cstate->delim[0]) != NULL)
  		ereport(ERROR,
***
*** 2951,2964 
  		int			input_len;
  
  		/* Make sure space remains in fieldvals[] */
! 		if (fieldno >= maxfields)
  			ereport(ERROR,
  	(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  	 errmsg("extra data after last expected column")));
  
  		/* Remember start of field on both input and output sides */
  		start_ptr = cur_ptr;
! 		fieldvals[fieldno] = output_ptr;
  
  		/*
  		 * Scan data for field,
--- 2970,2984 
  		int			input_len;
  
  		/* Make sure space remains in fieldvals[] */
! 		if (fieldno >= maxfields && ! cstate->ragged)
  			ereport(ERROR,
  	(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  	 errmsg("extra data after last expected column")));
  
  		/* Remember start of field on both input and output sides */
  		start_ptr = cur_ptr;
! 		if (fieldno < maxfields)
! 			fieldvals[fieldno] = output_ptr;
  
  		/*
  		 * Scan data for field,
***
*** 3045,3051 
  		/* Check whether raw input matched null marker */
  		input_len = end_ptr - start_ptr;
  		if (!saw_quote && input_len == cstate->null_print_len &&
! 			strncmp(start_ptr, cstate->null_print, input_len) == 0)
  			fieldvals[fieldno] = NULL;
  
  		fieldno++;
--- 3065,3072 
  		/* Check whether raw input matched null marker */
  		input_len = end_ptr - start_ptr;
  		if (!saw_quote && input_len == cstate->null_print_len &&
! 			strncmp(start_ptr, cstate->null_print, input_len) == 0 &&
! 			fieldno < maxfields)
  			fieldvals[fieldno] = NULL;
  
  		fieldno++;
***
*** 3059,3065 
  	Assert(*output_ptr == '\0');
  	cstate->attribute_buf.len = (output_ptr - cstate->attribute_buf.data);
  
! 	return fieldno;
  }
  
  
--- 3080,3092 
  	Assert(*output_ptr == '\0');
  	cstate->attribute_buf.len = (output_ptr - cstate->attribute_buf.data);
  
! 	/* for ragged input, set

Re: [HACKERS] Why does LOG have higher priority than ERROR and WARNING?

[Itagaki Takahiro]

Tom Lane  wrote:

> Itagaki Takahiro  writes:
> > Can I reorder them to ERROR > WARNING > LOG ?
> 
> No.  That was an intentional decision.  LOG is for stuff that we
> really want to get logged, in most cases.  ERROR is very often not
> that interesting, and WARNING even more so.

I think the decision is in hacker's viewpoint. Many times I see
DBAs are interested in only WARNING, ERROR and FATAL, but often
ignores LOG messages. We should use WARNING level for really important
message -- and also priority of WARNINGs should be higher than LOGs.

Another matter is that we use LOG level both cases of important
activity logging and mere performance or query logging. Maybe
we should have used another log level (PERFORMANCE?) for the
latter case, and its priority is less than WARNINGs and LOGs.

Regards,
---
ITAGAKI Takahiro
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] autovacuum_max_workers docs

[Joshua Tolley]

On Sun, Sep 13, 2009 at 10:54:21PM +0300, Peter Eisentraut wrote:
> On fre, 2009-09-11 at 07:39 -0600, Joshua Tolley wrote:
> While your discovery is accurate and the change makes it consistent with
> other similar parameters, note that the previous wording is also
> completely correct.  This while way of phrasing things is suboptimal.
> 
> I've committed it anyway for now.

Although I understand we also need a way to demonstrate which options can be
set interactively, and which can't, I'd love to see "changing this option
requires a restart" or "... a reload", if only because I'm always interpreting
the docs wrong in that respect. That said, if I ever come up with woeding I'm
especially proud of, I'll submit a less-trivial patch.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


signature.asc
Description: Digital signature

Re: [HACKERS] Rough draft: easier translation of psql help

[Tom Lane]

Peter Eisentraut  writes:
> Instead of translating the whole string, that is (picking a shorter
> example)

> N_("ALTER TEXT SEARCH PARSER name RENAME TO newname")

> we really only want to translate the placeholders, so it could look like
> this:

>appendPQExpBuffer(buf,
>   "ALTER TEXT SEARCH PARSER %s RENAME TO %s",
>   _("name"),
>   _("newname"));

> This is what the attached patch produces.

Seems like a reasonable idea.

> Comments?

I'm not sure what the "const" here is good for, and I can think of
some compilers that are likely to get confused too:

> + void (* const syntaxfunc)(PQExpBuffer); /* function that prints the 
> syntax associated with it */

Also, are you sure that code to identify the placeholders is robust?
Should you be defending against '%' in the syntax string?
Will the NLS infrastructure remember to build sql_help.c before
looking for strings?

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] [COMMITTERS] pgsql: Add Unicode support in PL/Python

[Andrew Dunstan]

Peter Eisentraut wrote:

On sön, 2009-09-13 at 10:07 -0400, Andrew Dunstan wrote:
  

Add Unicode support in PL/Python
  

The buildfarm seems quite unhappy with this patch.


I am completely unable to reproduce the buildfarm failures, even with
different Python versions and the exact configure options that the
buildfarm instances use.  Does anyone have an affected build and wants
to work through this with me?
  
I am going out shortly, but if you send me your ssh PK before I go I 
will set you an account up on dungbeetle which is failing and you can 
play there.



This is fixed now.

The reason this was not reproducible manually but showed up so
dramatically in the build farm is that almost all buildfarm machines use
SQL_ASCII as server encoding.  While it's evidently good that we have
some machines testing that, we should probably also have more machines
testing the UTF8 and other code paths.  Also, perhaps I'm missing
something, but it could have helped if the buildfarm logs showed the
locale and/or encoding somewhere.


  



Support for various locales and encodings is a relatively recent 
addition to the buildfarm, and many members have not yet updated. But 
dungbeetle has, for example. And you can see tests there run both in 
C/SQL_ASCII and en_US.utf8. See 
 
I know some of the boxes run at Sun (e.g. gothic_moth) also do checks in 
several locale/encoding combinations.


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] [COMMITTERS] pgsql: Add Unicode support in PL/Python

[Peter Eisentraut]

On sön, 2009-09-13 at 10:07 -0400, Andrew Dunstan wrote:
> >>> Add Unicode support in PL/Python
> >> The buildfarm seems quite unhappy with this patch.
> > I am completely unable to reproduce the buildfarm failures, even with
> > different Python versions and the exact configure options that the
> > buildfarm instances use.  Does anyone have an affected build and wants
> > to work through this with me?
> I am going out shortly, but if you send me your ssh PK before I go I 
> will set you an account up on dungbeetle which is failing and you can 
> play there.

This is fixed now.

The reason this was not reproducible manually but showed up so
dramatically in the build farm is that almost all buildfarm machines use
SQL_ASCII as server encoding.  While it's evidently good that we have
some machines testing that, we should probably also have more machines
testing the UTF8 and other code paths.  Also, perhaps I'm missing
something, but it could have helped if the buildfarm logs showed the
locale and/or encoding somewhere.


-- 
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] New features on 8.5 !!!!

[Marcos Luis Ortiz Valmaseda]

Thanks, for the your quick answer.
I'll study the way We can do this, to me is very important and very useful that 
PostgreSQL has this feature.

Regards


"The hurry is enemy of the success: for that reason...Be patient"

Ing. Marcos L. Ortiz Valmaseda
Línea Soporte y Despliegue
Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)

Linux User # 418229
PostgreSQL User
http://www.postgresql.org
http://www.planetpostgresql.org/
http://www.postgresql-es.org/


- Mensaje original -
De: "Heikki Linnakangas" 
Para: "Marcos Luis Ortiz Valmaseda" 
CC: pgsql-hackers@postgresql.org
Enviados: Domingo, 13 de Septiembre 2009 8:54:49 GMT -10:00 Hawai
Asunto: Re: [HACKERS] New features on 8.5 

Marcos Luis Ortiz Valmaseda wrote:
> Regards to all the list.
> Where I can find the new feautures that will be implemented on the 8.5 
> version?

See http://www.postgresql.org/developer/roadmap.

We're also doing preliminary developer-oriented alpha releases
throughout the 8.5 release cycle. You can look at the release notes of
those to see which features have been completed thus far, but for
features that haven't been completed yet, there is no authoritative list
of what will be included in the next release as that depends on what
individual developers decide to work on.

> Is there included the posibility on a tablespace restrict to a user to use a 
> certain space inside it?

No. If you search the archives, you'll find that that has been discussed
before but no-one has come up with a robust way to implement that.

Suggestions and patches are welcome, of course..

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RfD: more powerful "any" types

[decibel]

On Sep 12, 2009, at 5:54 PM, Andrew Dunstan wrote:

decibel wrote:

Speaking of concatenation...

Something I find sorely missing in plpgsql is the ability to put  
variables inside of a string, ie:


DECLARE
v_table text := ...
v_sql text;
BEGIN
v_sql := "SELECT * FROM $v_table";

Of course, I'm assuming that if it was easy to do that it would be  
done already... but I thought I'd just throw it out there.




Then use a language that supports variable interpolation in  
strings, like plperl, plpythonu, plruby  instead of plpgsql.



Which makes executing SQL much, much harder.

At least if we get sprintf dealing with strings might become a bit  
easier...

--
Decibel!, aka Jim C. Nasby, Database Architect  deci...@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828



--
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] COPY enhancements

[Tom Lane]

Josh Berkus  writes:
> It's not as if we don't have the ability to measure performance impact.
>  It's reasonable to make a requirement that new options to COPY
> shouldn't slow it down noticeably if those options aren't used.  And we
> can test that, and even make such testing part of the patch review.

Really?  Where is your agreed-on, demonstrated-to-be-reproducible
benchmark for COPY speed?

My experience is that reliably measuring performance costs in the
percent-or-so range is *hard*.  It's only after you've added a few of
them and they start to mount up that it becomes obvious that all those
insignificant additions really did cost something.

But in any case, I think that having a clear distinction between
"straight data import" and "data transformation" features is a good
thing.  COPY is already pretty much of an unmanageable monstrosity,
and continuing to accrete features into it without any sort of structure
is something we are going to regret.

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] COPY enhancements

[Josh Berkus]

Tom,

> [ shrug... ]  Everybody in the world is going to want their own little
> problem to be handled in the fast path.  And soon it won't be so fast
> anymore.  I think it is perfectly reasonable to insist that the fast
> path is only for "clean" data import.

Why?

No, really.

It's not as if we don't have the ability to measure performance impact.
 It's reasonable to make a requirement that new options to COPY
shouldn't slow it down noticeably if those options aren't used.  And we
can test that, and even make such testing part of the patch review.

But ... fault-tolerant COPY is one of our biggest user
requests/complaints.  At user group meetings and the like, I get asked
about it probably every third gathering of users I'm at.  While it's not
as critical as log-based replication, it's also not nearly as hard to
integrate and review.

I fully support the idea that we need to have the extended syntax for
these new COPY options.  But we should make COPY take an alternate path
for fault-tolerant COPY only if it's shown that adding these options
slows down database restore.

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Rough draft: easier translation of psql help

[Peter Eisentraut]

One of the main pains in translating PostgreSQL messages is translating
the SQL syntax synopses in psql.  Things like:

msgid ""
"[ WITH [ RECURSIVE ] with_query [, ...] ]\n"
"SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]\n"
"* | expression [ [ AS ] output_name ] [, ...]\n"
"INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table\n"
"[ FROM from_item [, ...] ]\n"
"[ WHERE condition ]\n"
"[ GROUP BY expression [, ...] ]\n"
"[ HAVING condition [, ...] ]\n"
"[ WINDOW window_name AS ( window_definition ) [, ...] ]\n"
"[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]\n"
"[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS
{ FIRST | "
"LAST } ] [, ...] ]\n"
"[ LIMIT { count | ALL } ]\n"
"[ OFFSET start [ ROW | ROWS ] ]\n"
"[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]\n"
"[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ]
[...] ]"

Especially when small things are changed from release to release,
figuring this out on the part of the translator is cumbersome and
error-prone.

Instead of translating the whole string, that is (picking a shorter
example)

N_("ALTER TEXT SEARCH PARSER name RENAME TO newname")

we really only want to translate the placeholders, so it could look like
this:

   appendPQExpBuffer(buf,
  "ALTER TEXT SEARCH PARSER %s RENAME TO %s",
  _("name"),
  _("newname"));

This is what the attached patch produces.

Comments?
diff --git a/src/bin/psql/Makefile b/src/bin/psql/Makefile
index d6c3a93..9fc1511 100644
--- a/src/bin/psql/Makefile
+++ b/src/bin/psql/Makefile
@@ -22,6 +22,7 @@ override CPPFLAGS := -I. -I$(srcdir) -I$(libpq_srcdir) -I$(top_srcdir)/src/bin/p
 OBJS=	command.o common.o help.o input.o stringutils.o mainloop.o copy.o \
 	startup.o prompt.o variables.o large_obj.o print.o describe.o \
 	psqlscan.o tab-complete.o mbprint.o dumputils.o keywords.o kwlookup.o \
+	sql_help.o \
 	$(WIN32RES)
 
 FLEXFLAGS = -Cfe
@@ -40,8 +41,9 @@ dumputils.c keywords.c: % : $(top_srcdir)/src/bin/pg_dump/%
 kwlookup.c: % : $(top_srcdir)/src/backend/parser/%
 	rm -f $@ && $(LN_S) $< .
 
+sql_help.c: sql_help.h ;
 sql_help.h: create_help.pl $(wildcard $(REFDOCDIR)/*.sgml)
-	$(PERL) $< $(REFDOCDIR) $@
+	$(PERL) $< $(REFDOCDIR) $*
 
 psqlscan.c: psqlscan.l
 ifdef FLEX
@@ -67,4 +69,4 @@ clean distclean:
 	rm -f psql$(X) $(OBJS) dumputils.c keywords.c kwlookup.c
 
 maintainer-clean: distclean
-	rm -f sql_help.h psqlscan.c
+	rm -f sql_help.h sql_help.c psqlscan.c
diff --git a/src/bin/psql/create_help.pl b/src/bin/psql/create_help.pl
index ea0e89c..ef055f0 100644
--- a/src/bin/psql/create_help.pl
+++ b/src/bin/psql/create_help.pl
@@ -14,7 +14,7 @@
 # enough that this worked, but this here is by no means an SGML
 # parser.
 #
-# Call: perl create_help.pl docdir sql_help.h
+# Call: perl create_help.pl docdir sql_help
 # The name of the header file doesn't matter to this script, but it
 # sure does matter to the rest of the source.
 #
@@ -22,26 +22,29 @@
 use strict;
 
 my $docdir = $ARGV[0] or die "$0: missing required argument: docdir\n";
-my $outputfile = $ARGV[1] or die "$0: missing required argument: output file\n";
+my $hfile = $ARGV[1] . '.h' or die "$0: missing required argument: output file\n";
+my $cfile = $ARGV[1] . '.c';
 
-my $outputfilebasename;
-if ($outputfile =~ m!.*/([^/]+)$!) {
-$outputfilebasename = $1;
+my $hfilebasename;
+if ($hfile =~ m!.*/([^/]+)$!) {
+$hfilebasename = $1;
 }
 else {
-$outputfilebasename = $outputfile;
+$hfilebasename = $hfile;
 }
 
-my $define = $outputfilebasename;
+my $define = $hfilebasename;
 $define =~ tr/a-z/A-Z/;
 $define =~ s/\W/_/g;
 
 opendir(DIR, $docdir)
 or die "$0: could not open documentation source dir '$docdir': $!\n";
-open(OUT, ">$outputfile")
-or die "$0: could not open output file '$outputfile': $!\n";
+open(HFILE, ">$hfile")
+or die "$0: could not open output file '$hfile': $!\n";
+open(CFILE, ">$cfile")
+or die "$0: could not open output file '$cfile': $!\n";
 
-print OUT
+print HFILE
 "/*
  * *** Do not change this file by hand. It is automatically
  * *** generated from the DocBook documentation.
@@ -56,15 +59,30 @@ print OUT
 
 #define N_(x) (x)/* gettext noop */
 
+#include \"postgres_fe.h\"
+#include \"pqexpbuffer.h\"
+
 struct _helpStruct
 {
 	const char	   *cmd;		/* the command name */
 	const char	   *help;		/* the help associated with it */
-	const char	   *syntax;		/* the syntax associated with it */
+	void (* const syntaxfunc)(PQExpBuffer);	/* function that prints the syntax associated with it */
 };
 
+";
+
+print CFILE
+"/*
+ * *** Do not change this file by hand. It is automatically
+ * *** generated from the DocBook documentation.
+ *
+ * generated by
+ * $^X $0 @ARGV
+ *
+ */
+
+#include \"$hfile\"
 
-static const struct _helpStruct QL_HELP[] = {
 ";
 
 my $maxlen = 0;
@@ -95,12 +113,18 @@ foreach my $file (sort readdir DIR) {
 	$cmddesc =~ s/\s+/ /g;
 

Re: [HACKERS] RfD: more powerful "any" types

[Hannu Krosing]

On Sun, 2009-09-13 at 21:50 +0200, Pavel Stehule wrote:
> Hello
> 
> >
> > ANY [TYPE] and SAME AS [TYPE OF] are syntactic sugar indeed, but they
> > are much more SQL-like than needing to write "any" or anyelement(n) as
> > argument type or return type
> >
> 
> I looked on possibilities in gram.y and I thing, type identifiers
> 
> "ANY TYPE" is possible without any problems (this should be synonym for 
> "any"),
> "SAME AS" needs add "same" keyword to col_name_keywords , i.e. "same"
> is prohibited for function names - it should be a problem
> 
> regards
> Pavel Stehule
> 
> I found so pgparser provide some ref type syntax via % symbol. So we
> can use following syntax:
> 
> CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b a%TYPE)
> RETURNS a%TYPE ...
> 
> It is not pretty like SAME AS, but I am sure, so this is doable
> (parser knows it now)
> 
> any other ideas?

Hmm, maybe try to make lexer recognize "SAME AS" as one token and then
deal with other cases of 'name AS' ?

Or make the syntax a little uglier, 

CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b TYPE OF a)

CREATE OR REPLACE FUNCTION foo(ANY TYPE, TYPE OF $1)


and maybe try

CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b TYPE OF a) 
RETURNS ARRAY OF TYPE OF a

instead of

CREATE OR REPLACE FUNCTION foo(a anyelement, b anyelement) 
RETURNS anyarray

> 
> regards
> Pavel Stehule


-- 
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability 
   Services, Consulting and Training



-- 
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] autovacuum_max_workers docs

[Peter Eisentraut]

On fre, 2009-09-11 at 07:39 -0600, Joshua Tolley wrote:
> The current docs for autovacuum_max_workers suggest it should be modifiable
> with a reload, unless I'm reading in awfully silly ways this morning (which
> isn't entirely out of the question). Anyway, in the 8.3.7 and 8.5devel
> instances I've tried, autovacuum_max_workers can only be set at server start.
> I propose this:
> 
> diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
> index 7c82835..26a8ddf 100644
> --- a/doc/src/sgml/config.sgml
> +++ b/doc/src/sgml/config.sgml
> @@ -3589,8 +3589,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH 
> csv;
> 
>  Specifies the maximum number of autovacuum processes (other than the
>  autovacuum launcher) which may be running at any one time.  The 
> default
> -is three.  This parameter can only be set in
> -the postgresql.conf file or on the server command line.
> +is three.  This parameter can only be set at server start.
> 
>
>   

While your discovery is accurate and the change makes it consistent with
other similar parameters, note that the previous wording is also
completely correct.  This while way of phrasing things is suboptimal.

I've committed it anyway for now.


-- 
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] RfD: more powerful "any" types

[Pavel Stehule]

Hello

>
> ANY [TYPE] and SAME AS [TYPE OF] are syntactic sugar indeed, but they
> are much more SQL-like than needing to write "any" or anyelement(n) as
> argument type or return type
>

I looked on possibilities in gram.y and I thing, type identifiers

"ANY TYPE" is possible without any problems (this should be synonym for "any"),
"SAME AS" needs add "same" keyword to col_name_keywords , i.e. "same"
is prohibited for function names - it should be a problem

regards
Pavel Stehule

I found so pgparser provide some ref type syntax via % symbol. So we
can use following syntax:

CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b a%TYPE)
RETURNS a%TYPE ...

It is not pretty like SAME AS, but I am sure, so this is doable
(parser knows it now)

any other ideas?

regards
Pavel Stehule

-- 
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] New features on 8.5 !!!!

[Heikki Linnakangas]

Marcos Luis Ortiz Valmaseda wrote:
> Regards to all the list.
> Where I can find the new feautures that will be implemented on the 8.5 
> version?

See http://www.postgresql.org/developer/roadmap.

We're also doing preliminary developer-oriented alpha releases
throughout the 8.5 release cycle. You can look at the release notes of
those to see which features have been completed thus far, but for
features that haven't been completed yet, there is no authoritative list
of what will be included in the next release as that depends on what
individual developers decide to work on.

> Is there included the posibility on a tablespace restrict to a user to use a 
> certain space inside it?

No. If you search the archives, you'll find that that has been discussed
before but no-one has come up with a robust way to implement that.

Suggestions and patches are welcome, of course..

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: [COMMITTERS] pgsql: On Windows, when a file is deleted and another process still has

[Heikki Linnakangas]

Tom Lane wrote:
> Magnus Hagander  writes:
>> On Fri, Sep 11, 2009 at 10:44, Heikki Linnakangas
>>> Here's a patch implementing that, and changing pgrename() to check for
>>> ERROR_SHARING_VIOLATION and ERROR_LOCK_VIOLATION like pgwin32_open()
>>> does, instead of ERROR_ACCESS_DENIED.
> 
>> I have definitely seen AV programs return access deniderather than
>> sharing violation more than once for temporary errors. How about we
>> keep the access denied one as well?
> 
> +1 ... presumably the original coding was tested in *some* environment.

Ok, I've committed that. Per quick discussion with Magnus, I also
lowered the timeout to 10s.

Luke, although your immediate problem was solved by the previous patch
already, this touched the same pieces of code, so you might want to
fetch the latest sources and retest if you want to be sure. (I did test
it myself..)

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] New features on 8.5 !!!!

[Marcos Luis Ortiz Valmaseda]

Regards to all the list.
Where I can find the new feautures that will be implemented on the 8.5 version?

Is there included the posibility on a tablespace restrict to a user to use a 
certain space inside it?

For example in Oracle you can do this:
CREATE TABLESPACE tb_space1
LOCATION '/mnt/Data'
OWNER 'dbuser'
USER peter 20 MB
USER tom 50 MB

This would be very useful on PostgreSQL

Regards

"The hurry is enemy of the success: for that reason...Be patient"

Ing. Marcos L. Ortiz Valmaseda
Línea Soporte y Despliegue
Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)

Linux User # 418229
PostgreSQL User
http://www.postgresql.org
http://www.planetpostgresql.org/
http://www.postgresql-es.org/


-- 
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] [COMMITTERS] pgsql: Add Unicode support in PL/Python

[Andrew Dunstan]

Peter Eisentraut wrote:

On lör, 2009-09-12 at 21:34 -0400, Tom Lane wrote:
  

pet...@postgresql.org (Peter Eisentraut) writes:


Log Message:
---
Add Unicode support in PL/Python
  

The buildfarm seems quite unhappy with this patch.



I am completely unable to reproduce the buildfarm failures, even with
different Python versions and the exact configure options that the
buildfarm instances use.  Does anyone have an affected build and wants
to work through this with me?

  



I am going out shortly, but if you send me your ssh PK before I go I 
will set you an account up on dungbeetle which is failing and you can 
play there.


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] [COMMITTERS] pgsql: Add Unicode support in PL/Python

[Peter Eisentraut]

On lör, 2009-09-12 at 21:34 -0400, Tom Lane wrote:
> pet...@postgresql.org (Peter Eisentraut) writes:
> > Log Message:
> > ---
> > Add Unicode support in PL/Python
> 
> The buildfarm seems quite unhappy with this patch.

I am completely unable to reproduce the buildfarm failures, even with
different Python versions and the exact configure options that the
buildfarm instances use.  Does anyone have an affected build and wants
to work through this with me?


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] CREATE LIKE INCLUDING COMMENTS and STORAGES

[Brendan Jurd]

2009/9/9 Itagaki Takahiro :
> Alvaro Herrera  wrote:
>> This INCLUDING STORAGE is supposed to copy reloptions?
>
> No. It copies only storage parameters of columns to control TOAST policy.
> It might be good to have some features to copy reloptions with convenient
> way, but it will be done in another patch.
>
>> to me it sounds like it's copying the
>> underlying storage i.e. data, which would be very surprising.  What
>> about "INCLUDING STORAGE OPTIONS"?

It *would* be very surprising.  An option to include data would
probably be called "INCLUDING DATA" =)

>
> Hmm, but we have the following syntax already:
>    ALTER TABLE table ALTER COLUMN column SET STORAGE ...
> Do you also think it should be "SET STORAGE OPTION ..." ?
>

Personally, I think INCLUDING STORAGE makes as much sense as you can
expect using just one word, and as Itagaki-san points out it
correlates well with the syntax for ALTER COLUMN.

Cheers,
BJ

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] WIP: generalized index constraints

[Brendan Jurd]

2009/8/21 Brendan Jurd :
> 2009/8/21 Jeff Davis :
>> On Fri, 2009-08-21 at 12:23 +1000, Brendan Jurd wrote:
>>> The current behaviour seems to be predicated on the unique constraint
>>> being an integral part of the index itself.  While this might be true
>>> from a system catalog point of view (pg_index.indisunique), if a user
>>> says that they want to copy a table's structure INCLUDING INDEXES
>>> EXCLUDING CONSTRAINTS then IMO they've made their intention perfectly
>>> clear.  They'd expect it to create an index sans the unique
>>> constraint.  Ignoring the user's intention and copying the index as-is
>>> (including the unique constraint) would be unfriendly.
>>
>> I don't have strong feelings either way. I think that's probably a
>> separate patch, and a fairly small patch.
>>
>
> Yeah, as I was writing the above I was thinking that it might end up a
> separate patch.  However I was also concerned that it might be less
> disruptive if we implement your patch with the less-astonishing
> behaviour and fix the unique index case in passing, than to commit
> your patch with the bad behavior and then fix both.
>
> Up to you.
>

Hi Jeff,

Any update on this patch?  The discussion appeared to trail off around
21 Aug with some inconclusive thoughts about handling the corner cases
in CREATE TABLE LIKE.

The September CF starts in a couple of days, so this patch is in
danger of missing the boat.

The unresolved points seem to be:

 * What to do about INCLUDING INDEXES EXCLUDING CONSTRAINTS --
Postgres gets this wrong for unique indexes currently.  Should we
persist with the existing behaviour or fix it as part of this patch?
My personal feeling was +1 for fixing it in this patch.

 * Should we emit some sort of message when the user specifies
INCLUDING INDEXES or INCLUDING CONSTRAINTS but not both?  I didn't
have strong feelings about this one but there was some differing
thoughts about what log level to use.  I thought NOTICE but Alvaro
reckons we've got too many of those already.  Tom mentioned the
suggested (but unimplemented) NOVICE level, which seems like a good
move but doesn't resolve the problem of what to do in this patch.  One
option would be to add a message at the NOTICE level with a TODO to
downgrade it to NOVICE if/when that becomes available.

Cheers,
BJ

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers