Re: [BUGS] BUG #5136: Please drop the string literal syntax for CREATE FUNCTION ...
On lör, 2009-10-24 at 14:01 +, Timothy Madden wrote: > Can the string literal syntax for CREATE FUNCTION please, please be dropped > ... ? > > http://www.postgresql.org/docs/8.4/interactive/plpgsql-structure.html > > It is not ANSI/ISO and so annoying for compatibility. Whatever is inside the string literal is also not ANSI/ISO, so why would it make a difference? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5136: Please drop the string literal syntax for CREATE FUNCTION ...
On Sun, Oct 25, 2009 at 12:42 PM, Peter Eisentraut wrote: > On lör, 2009-10-24 at 14:01 +, Timothy Madden wrote: > > Can the string literal syntax for CREATE FUNCTION please, please be > dropped > > ... ? > [...] > > It is not ANSI/ISO and so annoying for compatibility. > > Whatever is inside the string literal is also not ANSI/ISO, so why would > it make a difference? > > At least the function declaration syntax would be right. I think it would be an important difference; this string literal idea is so strange and unnatural ... And why the function body would not be standard ? Do you mean it can never be for some reason ? How about a simple function ? Thank you, Timothy Madden
Re: [BUGS] BUG #5136: Please drop the string literal syntax for CREATE FUNCTION ...
2009/10/25 Timothy Madden : > > > On Sun, Oct 25, 2009 at 12:42 PM, Peter Eisentraut wrote: >> >> On lör, 2009-10-24 at 14:01 +, Timothy Madden wrote: >> > Can the string literal syntax for CREATE FUNCTION please, please be >> > dropped >> > ... ? > > [...] >> >> > It is not ANSI/ISO and so annoying for compatibility. >> >> Whatever is inside the string literal is also not ANSI/ISO, so why would >> it make a difference? >> > At least the function declaration syntax would be right. I think it would be > an important difference; this string literal idea is so strange and > unnatural ... > > And why the function body would not be standard ? Because standard knows only SQL/PSM language for SQL procedures. Others databases support only one language for sql procedures (PL/SQL, T-SQL). But PostgreSQL supports plpgsql, plperl, plpython as sql procedures - and I am sure, so standard doesnt calculate with multilangual environments. Regards Pavel Stehule Do you mean it can never > be for some reason ? How about a simple function ? > > Thank you, > Timothy Madden > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5137: Upgrade policy clarification
The following bug has been logged online: Bug reference: 5137 Logged by: Peter Bengtson Email address: pe...@peterbengtson.com PostgreSQL version: 8.3.8 Operating system: Debian, Mac OS X Description:Upgrade policy clarification Details: It would be good if you to the page describing how a db dump is required only for major verson upgrades, e.g. from 8.3.x to 8.4.x but not from, say, 8.3.7 to 8.3.8, could add a proviso: a DB dump *is* necessary when upgrading from a server compiled for 32 bits even to the same version compiled for 64 bit architectures. I recently spent about 12 hours getting my development system in order again after an upgrade from Leopard to Snow Leopard, simply because I believed your upgrade policy page. It would be nice if other people didn't have to go through that process. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5137: Upgrade policy clarification
On sön, 2009-10-25 at 13:52 +, Peter Bengtson wrote: > It would be good if you to the page describing how a db dump is required > only for major verson upgrades, e.g. from 8.3.x to 8.4.x but not from, say, > 8.3.7 to 8.3.8, could add a proviso: a DB dump *is* necessary when upgrading > from a server compiled for 32 bits even to the same version compiled for 64 > bit architectures. > > I recently spent about 12 hours getting my development system in order again > after an upgrade from Leopard to Snow Leopard, simply because I believed > your upgrade policy page. It would be nice if other people didn't have to go > through that process. Which page exactly are you talking about? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5136: Please drop the string literal syntax for CREATE FUNCTION ...
On Sun, Oct 25, 2009 at 6:17 PM, Pavel Stehule wrote:
> 2009/10/25 Timothy Madden :
> >
> >
> > On Sun, Oct 25, 2009 at 12:42 PM, Peter Eisentraut
> wrote:
> >>
> >> On lör, 2009-10-24 at 14:01 +, Timothy Madden wrote:
> >> > Can the string literal syntax for CREATE FUNCTION please, please be
> >> > dropped
> >> > ... ?
> >
> > [...]
> >>
> >> > It is not ANSI/ISO and so annoying for compatibility.
> >>
> >> Whatever is inside the string literal is also not ANSI/ISO, so why would
> >> it make a difference?
> >>
> > At least the function declaration syntax would be right. I think it would
> be
> > an important difference; this string literal idea is so strange and
> > unnatural ...
> >
> > And why the function body would not be standard ?
>
> Because standard knows only SQL/PSM language for SQL procedures.
> Others databases support only one language for sql procedures (PL/SQL,
> T-SQL). But PostgreSQL supports plpgsql, plperl, plpython as sql
> procedures - and I am sure, so standard doesnt calculate with
> multilangual environments.
>
Ok I get it. So Posgres also offers perl and python in addition to SQL.
But at least for SQL, which is included and defined in the standard, could
the syntax be made conforming ? As an alternative to the one already
used (to offer the additional langauges) ?
So that the additional languages are provided as before, and the
standard-definded one still follows the standard ?
Thank you,
Timothy Madden
P.S. :
As a note, actually the standard provides for other languages, with the
procedures only referenced as external (pre-compiled) instead of
defined/scripted in line.
If anyone is interested you have below the CREATE FUNCTION and
CREATE PROCEDURE syntax definition from SQL-3
(SQL-99, Part 2 - Foundation).
::=
PROCEDURE
::=
{ | }
::=
FUNCTION
[ ]
::=
|
| SPECIFIC
|
|
|
|
|
::=
|
::=
::=
EXTERNAL [ NAME ]
[ ]
[ ]
::=
LANGUAGE
::=
ADA | C | COBOL | FORTRAN | MUMPS | PASCAL | PLI | SQL
::=
DYNAMIC RESULT SETS
::=
PARAMETER STYLE
::= STATIC DISPATCH
::= RETURNS [ ]
::= CAST FROM
::=
[ ]
::= [ ]
::=
SQL
| GENERAL
::=
DETERMINISTIC
| NOT DETERMINISTIC
::=
NO SQL
| CONTAINS SQL
| READS SQL DATA
| MODIFIES SQL DATA
Re: [BUGS] BUG #5136: Please drop the string literal syntax for CREATE FUNCTION ...
Timothy Madden writes: > Ok I get it. So Posgres also offers perl and python in addition to SQL. > But at least for SQL, which is included and defined in the standard, could > the syntax be made conforming ? I think you still haven't got the point: there is *no* function language that we offer that exactly matches what the spec has got. Not using string-literal syntax, difficult as that is already, would probably represent about 1% of the work needed to implement what the spec suggests. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5136: Please drop the string literal syntax for CREATE FUNCTION ...
On Mon, Oct 26, 2009 at 12:13 AM, Tom Lane wrote: > Timothy Madden writes: > > Ok I get it. So Posgres also offers perl and python in addition to SQL. > > But at least for SQL, which is included and defined in the standard, > could > > the syntax be made conforming ? > > I think you still haven't got the point: there is *no* function language > that we offer that exactly matches what the spec has got. Not using > string-literal syntax, difficult as that is already, would probably > represent about 1% of the work needed to implement what the spec > suggests. > I would like to put to good use the statements and expression that do match (SELECT/INSERT/UPDATE/ ...), and the very function declaration syntax is getting in the way, making even those matches now useless ... Thank you, Timothy Madden
[BUGS] Postmaster hangs
I've been dealing with a postgres (8.3.8.1.fc10) problem for a while that has become quite frustrating in recent weeks. I have a set of tables, one of which (geonames) is very large. This contains the geonames geographical information database -- about 7 million records: CREATE TABLE geonames ( geonameid BIGINT PRIMARY KEY, name VARCHAR(200), asciiname VARCHAR(200), alternatenames VARCHAR(4000), latitude DOUBLE PRECISION, longitude DOUBLE PRECISION, geoblock0 SMALLINT, geoblock1 SMALLINT, geoblock2 INTEGER, geoblock3 INTEGER, geoblock0_latitude SMALLINT, geoblock0_longitude SMALLINT, geoblock1_latitude SMALLINT, geoblock1_longitude SMALLINT, geoblock2_latitude SMALLINT, geoblock2_longitude SMALLINT, geoblock3_latitude SMALLINT, geoblock3_longitude SMALLINT, feature_class CHAR(1), feature_code VARCHAR(10), country_code CHAR(2), cc2 VARCHAR(60), admin1_code VARCHAR(20), admin2_code VARCHAR(80), admin3_code VARCHAR(20), admin4_code VARCHAR(20), name_admin1_code VARCHAR(221), name_admin1_name VARCHAR(601), population BIGINT, elevation INTEGER, gtopo30 INTEGER, timezone VARCHAR(255), modification_date TIMESTAMP, FOREIGN KEY (feature_class) REFERENCES feature_classes(class), FOREIGN KEY (feature_class, feature_code) REFERENCES feature_codes(class, code), FOREIGN KEY (country_code) REFERENCES countries(iso), FOREIGN KEY (country_code, admin1_code) REFERENCES admin1_codes(country, admin1_code), FOREIGN KEY (country_code, admin1_code, admin2_code) REFERENCES admin2_codes(country, admin1_code, admin2_code), FOREIGN KEY (country_code, admin1_code, admin2_code, admin3_code) REFERENCES admin3_codes(country, admin1_code, admin2_code, admin3_code), FOREIGN KEY (country_code, admin1_code, admin2_code, admin3_code, admin4_code) REFERENCES admin4_codes(country, admin1_code, admin2_code, admin3_code, admin4_code), FOREIGN KEY (timezone) REFERENCES timezones(name) ); I'm in the middle of trying to set the "name_admin1"code" and "name_admin1_name" fields. Ideally, I'd like to use the following command: update geonames set name_admin1_code = upper(name || ',' || admin1_code), name_admin1_name = upper(name || ',' || (select description from admin1_codes where admin1_codes.admin1_code = geonames.admin1_code AND admin1_codes.country = geonames.country_code)); However, this command is *guaranteed* to lock up postmaster (more on that in just a second). As a consequence, I'm forced to break up the command into smaller units, such as: update geonames set name_admin1_code = upper(name || ',' || admin1_code), name_admin1_name = upper(name || ',' || (select description from admin1_codes where admin1_codes.admin1_code = geonames.admin1_code AND admin1_codes.country = geonames.country_code)) WHERE admin1_code = 'WV'; It'll get through about three or four of them (out of hundreds) before it locks up. Now, before lockup, postmaster is very active. It shows up on top. The computer's hard drives clack nonstop. Etc. But once it locks up (without warning), all of that stop. Postmaster does nothing. The computer goes silent. I can't ctrl-break the psql process. If I try to start a new psql process, it won't get past the password prompt -- psql will hang. All Apache processes involving postgres queries hang. The postgres server cannot be restarted by any normal means (the only solution I've found that works is a reboot). And so forth. This isn't the first time I've had this problem. When setting my geoblock fields, I had the exact same problem -- and this was before I had any apache cgi scripts that would access the database. So whatever is jamming postgres seems to be entirely internal to the single update process. I've just been living with this problem until now, but tonight I got sick enough of it that I felt I had to report it. Is this a known bug? Are there workarounds? - Karen -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Postmaster hangs
Karen Pease writes: > It'll get through about three or four of them (out of hundreds) before > it locks up. Now, before lockup, postmaster is very active. It shows > up on top. The computer's hard drives clack nonstop. Etc. But once it > locks up (without warning), all of that stop. Postmaster does nothing. > The computer goes silent. I can't ctrl-break the psql process. If I > try to start a new psql process, it won't get past the password prompt > -- psql will hang. All Apache processes involving postgres queries > hang. The postgres server cannot be restarted by any normal means (the > only solution I've found that works is a reboot). And so forth. This sounds to me like it's a kernel problem, possibly triggered by misbehaving disk hardware. What you might try to confirm is a kill -9 on whichever postgres backend seems to be stuck. If that fails to remove the process, then it's definitely a kernel issue --- try googling "uninterruptible disk wait" and similar phrases. The cases that I've run into personally have been due to poor error handling for a disk failure condition in a kernel-level disk driver. If that's what it is for you, the bottom-level problem might be an unreadable disk block somewhere. Or it might just be a garden variety kernel bug. What's the platform? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Postmaster hangs
kill -9 does kill postmaster (or at least seems to). But I can't figure out a way to get it restarted without a reboot -- I don't know what I'm missing. The Fedora postgres restart scripts don't do the trick, and I couldn't get it to work with pg_ctl either. kill -9 doesn't work on the locked up httpd processes. So that has to have the system restarted. [m...@chmmr]$ cat /proc/version Linux version 2.6.27.37-170.2.104.fc10.i686 (mockbu...@xenbuilder4.fedora.phx.redhat.com) (gcc version 4.3.2 20081105 (Red Hat 4.3.2-7) (GCC) ) #1 SMP Mon Oct 12 22:01:53 EDT 2009 Postgres is by default in /var/lib/pgsql. When / started running out of space, I moved it to /scratch and symlinked: lrwxrwxrwx 1 root root 15 2009-09-11 16:57 pgsql -> /scratch/pgsql// / is on md0 and is RAID-1. /scratch is on md1 and is RAID-6: [m...@chmmr]$ df -h FilesystemSize Used Avail Use% Mounted on /dev/md0 64G 42G 18G 71% / /dev/md1 2.5T 2.2T 239G 91% /scratch /dev/sdb1 190M 38M 143M 21% /boot /dev/sde1 190M 86M 95M 48% /boot2 /dev/sdd1 190M 86M 95M 48% /boot3 /dev/sda1 190M 86M 95M 48% /boot4 /dev/sdc1 190M 86M 95M 48% /boot5 tmpfs1000M 0 1000M 0% /dev/shm [m...@chmmr]$ cat /proc/mdstat Personalities : [raid1] [raid6] [raid5] [raid4] md1 : active raid6 sde4[0] sdc4[4] sda4[3] sdb4[2] sdd4[1] 2722005120 blocks level 6, 64k chunk, algorithm 2 [5/5] [U] md0 : active raid1 sde3[0] sdc3[4] sda3[3] sdb3[2] sdd3[1] 67119488 blocks [5/5] [U] unused devices: Both filesystems are EXT-4. Thanks for your help! - Karen On Sun, 2009-10-25 at 23:13 -0400, Tom Lane wrote: > Karen Pease writes: > > It'll get through about three or four of them (out of hundreds) before > > it locks up. Now, before lockup, postmaster is very active. It shows > > up on top. The computer's hard drives clack nonstop. Etc. But once it > > locks up (without warning), all of that stop. Postmaster does nothing. > > The computer goes silent. I can't ctrl-break the psql process. If I > > try to start a new psql process, it won't get past the password prompt > > -- psql will hang. All Apache processes involving postgres queries > > hang. The postgres server cannot be restarted by any normal means (the > > only solution I've found that works is a reboot). And so forth. > > This sounds to me like it's a kernel problem, possibly triggered by > misbehaving disk hardware. What you might try to confirm is a kill -9 > on whichever postgres backend seems to be stuck. If that fails to > remove the process, then it's definitely a kernel issue --- try googling > "uninterruptible disk wait" and similar phrases. > > The cases that I've run into personally have been due to poor error > handling for a disk failure condition in a kernel-level disk driver. > If that's what it is for you, the bottom-level problem might be an > unreadable disk block somewhere. Or it might just be a garden variety > kernel bug. What's the platform? > > regards, tom lane > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5136: Please drop the string literal syntax for CREATE FUNCTION ...
2009/10/25 Timothy Madden :
>
> On Sun, Oct 25, 2009 at 6:17 PM, Pavel Stehule
> wrote:
>>
>> 2009/10/25 Timothy Madden :
>> >
>> >
>> > On Sun, Oct 25, 2009 at 12:42 PM, Peter Eisentraut
>> > wrote:
>> >>
>> >> On lör, 2009-10-24 at 14:01 +, Timothy Madden wrote:
>> >> > Can the string literal syntax for CREATE FUNCTION please, please be
>> >> > dropped
>> >> > ... ?
>> >
>> > [...]
>> >>
>> >> > It is not ANSI/ISO and so annoying for compatibility.
>> >>
>> >> Whatever is inside the string literal is also not ANSI/ISO, so why
>> >> would
>> >> it make a difference?
>> >>
>> > At least the function declaration syntax would be right. I think it
>> > would be
>> > an important difference; this string literal idea is so strange and
>> > unnatural ...
>> >
>> > And why the function body would not be standard ?
>>
>> Because standard knows only SQL/PSM language for SQL procedures.
>> Others databases support only one language for sql procedures (PL/SQL,
>> T-SQL). But PostgreSQL supports plpgsql, plperl, plpython as sql
>> procedures - and I am sure, so standard doesnt calculate with
>> multilangual environments.
>
>
> Ok I get it. So Posgres also offers perl and python in addition to SQL.
> But at least for SQL, which is included and defined in the standard, could
> the syntax be made conforming ? As an alternative to the one already
> used (to offer the additional langauges) ?
>
look on plsqlpsm - it is implementation of SQL/PSM.
> So that the additional languages are provided as before, and the
> standard-definded one still follows the standard ?
>
>
> Thank you,
> Timothy Madden
>
>
> P.S. :
> As a note, actually the standard provides for other languages, with the
> procedures only referenced as external (pre-compiled) instead of
> defined/scripted in line.
this is external languages. It is different.
>
> If anyone is interested you have below the CREATE FUNCTION and
> CREATE PROCEDURE syntax definition from SQL-3
> (SQL-99, Part 2 - Foundation).
>
> ::=
> PROCEDURE declaration list>
>
>
>
> ::=
> { | }
>
>
> ::=
> FUNCTION list>
>
>
> [ ]
>
> ::=
>
> |
> | SPECIFIC
> |
> |
> |
> |
> |
>
> ::=
>
> |
>
> ::=
>
> ::=
> EXTERNAL [ NAME ]
> [ ]
> [ ]
>
> ::=
> LANGUAGE
>
> ::=
> ADA | C | COBOL | FORTRAN | MUMPS | PASCAL | PLI | SQL
>
> ::=
> DYNAMIC RESULT SETS
>
> ::=
> PARAMETER STYLE
>
> ::= STATIC DISPATCH
> ::= RETURNS [ ]
> ::= CAST FROM
>
> ::=
> [ ]
>
> ::= [ ]
>
> ::=
> SQL
> | GENERAL
>
> ::=
> DETERMINISTIC
> | NOT DETERMINISTIC
>
> ::=
> NO SQL
> | CONTAINS SQL
> | READS SQL DATA
> | MODIFIES SQL DATA
>
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5136: Please drop the string literal syntax for CREATE FUNCTION ...
2009/10/25 Timothy Madden : > > > On Mon, Oct 26, 2009 at 12:13 AM, Tom Lane wrote: >> >> Timothy Madden writes: >> > Ok I get it. So Posgres also offers perl and python in addition to SQL. >> > But at least for SQL, which is included and defined in the standard, >> > could >> > the syntax be made conforming ? >> >> I think you still haven't got the point: there is *no* function language >> that we offer that exactly matches what the spec has got. Not using >> string-literal syntax, difficult as that is already, would probably >> represent about 1% of the work needed to implement what the spec >> suggests. > > > I would like to put to good use the statements and expression that do match > (SELECT/INSERT/UPDATE/ ...), and the very function declaration syntax is > getting in the way, making even those matches now useless ... ??? Pavel > > Thank you, > Timothy Madden > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
