Re: [GENERAL] citext.so
citext is not a new contrib module, I have been using it since 7.4 I believe it is being built into core in 8.4. I have it compiled on 32 bit 8.2, and 64 bit 8.1 and 8.3 My 8.3 compilation says it is incompatible and the 8.1 compilation says that it is missing the magic block. It is compiled with the PG_MODULE_MAGIC, so I assume the problem is actually with version incompatibilities. Sim > citext is a new *8.4* contrib module. > > > Osvaldo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] help with data recovery from injected UPDATE
On Sun, 2009-06-14 at 10:32 -0400, Gus Gutoski wrote: > 0. Shortly after the corruption on June 9, 2009, I shut down the > server and backed up the entire data directory. The recovery > procedure described herein begins with this file system backup. > 1. The most recent non-corrupted snapshot of the database is a pg_dump > from May 13, 2009. (I don't have any file system backups from before > the corruption.) I restored the database to this snapshot by > executing the commands from the May 13 pg_dump on the June 9 corrupted > data. > 2. I removed the files in the pg_xlog directory and replaced them > with the contents of pg_xlog from the corrupted file system backup > from June 9. That really, REALLY won't work. It just doesn't work like that. You're trying to use a block-level restore process (the transaction logs) with a base backup that's at a much higher level, and isn't block-for-block the same as the old database files. Additionally, you're trying to do so over a known corrupt database. The only thing that confuses me is how you convinced Pg to run recovery using the xlog files you put in place. It should've refused, surely? > I guess it's too much to ask postmaster to do a PITR from a pg_dump > backup, as opposed to a file system backup. Bummer. Yep. No hope. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Opinions on how to Integrate Transactions
On Sun, 2009-06-14 at 11:14 -0400, APseudoUtopia wrote: > associated forum posts. From what I've read on transactions, they > would be the solution to my problems. Yes, but proper use of transactions - and a proper understanding of transactions - will help you a lot more than that. I very, very strongly recommend that you read the PostgreSQL manual. It will not only teach you about PostgreSQL, but should teach you a lot about how to use a relational database effectively. It's really important to understand how transactions operate in a concurrent environment, how locking works (and where it doesn't apply), etc. Understanding MVCC is also rather helpful. The PostgreSQL manual will teach you all these things and a LOT more. > 1. Is there a way to list active transactions on all databases? SELECT * FROM pg_stat_activity; Filter the results as desired. > I'd > like a way to see if there are any "idle" transactions that are just > hanging there. Is there a way to "kill" them from the console? pg_cancel_backend(...) However, you should not have idle transactions in the first place. An idle transaction indicates an application bug unless your application is designed to hold transactions open while waiting for user input. If it is meant to, then killing those idle transactions would be wrong, since the app expects them to be there later. Holding transactions open for long periods, like waiting for user input, causes all sorts of issues, though, and is best avoided. > 2. I have a PHP class which communicates with the DB, which is > included into every other page on the site. Should I just add BEGIN > and COMMIT at the beginning and end of the class? This way, I wont > need to edit every single page on my site to include BEGIN and COMMIT? > Or will that type of "blind" transactions cause problems? It's a much better idea to look at how you're actually communicating with the database, examine each query or set of queries, and see what needs to be wrapped in a transaction. In lots of cases it will be fine to just wrap the lot in begin/commit. In some cases, though, you might have one operation that you want to succeed even if later ones fail. Additionally, if you have single independent statements, there's no need to wrap them in BEGIN/COMMIT, since Pg creates an implicit transaction for each statement if one isn't already running. > 3. What happens when there IS an error? Do I need to run ROLLBACK > every time? Or will it automatically reset when the connection is > closed when the PHP script dies? If the connection is closed while a transaction is open, an implicit ROLLBACK is issued by the backend. However, you should always close your transactions properly, both to ensure that data that should get committed does get committed, and so that if you start using connection pooling (which you probably will) you're not driven insane by issues with transactions left open. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Opinions on how to Integrate Transactions
Hey,
I'm new to using transactions. I'd like to integrate them into my PHP
application to prevent a few issues we've been having. Such as, some
queries in the beginning of the php script running, then an error
occurring, preventing queries toward the bottom of the script from
running. It's leaving me with things such as a forum topic without any
associated forum posts. From what I've read on transactions, they
would be the solution to my problems.
As far as I understand it, the transactions may solve your problem. Or
more precisely, symptomps of your problem. I don't say you should not
use transactions, but a proper solution would be to fix the original
problem causing the error.
Why do the statements fail? I usually see this when the user's input is
not verified properly (not all required information entered, invalid
inputs, invalid characters, etc.). If this is the case, add the
verification of inputs (and then maybe the transaction).
So I have a couple questions about them.
1. Is there a way to list active transactions on all databases? I'd
like a way to see if there are any "idle" transactions that are just
hanging there. Is there a way to "kill" them from the console?
No, AFAIK it's not possible to list currently running transactions. You
can list currently running statements (see the pg_stat_activity), and
various information about the database (number of commited / rolled back
transactions etc. - see the pg_stat_database).
But I don't see why you'd be interested in a currently running
transactions, as:
(a) If the client disconnects without explicitly commiting or rolling
back a transaction, the transaction is rolled back automatically
(b) If the client is connected, and the transaction is still open, it
means a SQL statement is running (and you can see it in the
pg_stat_activity), or the client (PHP script) is performing something
else. So you can't kill it anyway as you don't know if the script will
continue or what.
2. I have a PHP class which communicates with the DB, which is
included into every other page on the site. Should I just add BEGIN
and COMMIT at the beginning and end of the class? This way, I wont
need to edit every single page on my site to include BEGIN and COMMIT?
Or will that type of "blind" transactions cause problems?
What do you mean by 'beginning and end of a class'? A transaction should
demarcate an atomic piece of functionality, so we it's impossible to say
where to put 'begin / commit' without a proper analysis of your code.
The simplest solution is probably to put 'begin' right after opening the
connection to the database, and 'commit' right before the disconnect.
This way you'll enclose the whole page into a single transaction, so you
won't get the 'partially created forums' and so on.
3. What happens when there IS an error? Do I need to run ROLLBACK
every time? Or will it automatically reset when the connection is
closed when the PHP script dies?
If there is an error, it's impossible to run a commit (unless you use
'rollback to' statement). More precisely - it's possible to execute
'COMMIT' but the transaction will replace it with 'ROLLBACK'. So for
example this:
CREATE TABLE Test (id integer);
BEGIN;
INSERT INTO Test VALUES (1);
INSERT INTO Test VALUES ('aaa');
COMMIT;
won't insert anything into the 'Test' table (it'll remain empty).
Thanks for the advice.
regards
Tomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] running pg_dump from python
Garry Saddington writes:
>> import os
>> os.popen("c:/scholarpack/postgres/bin/pg_dump scholarpack >
>> c:/scholarpack/ancillary/scholarpack.sql")
> scholarpack but no OS user scholarpack, but why should it create a file
> then not fill the contents?
Because that's exactly what will happen if pg_dump fails. The shell
creates the empty output file and then tries to run the program.
If program fails before sending anything to the output file, that's
what you're left with.
The real problem with this script is it's not even considering the
possibility of program failure --- it's not checking for an error exit
code, much less ensuring that error messages go someplace where you
could look at them to find out what the problem is.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] running pg_dump from python
Scott Mead wrote:
On Sun, Jun 14, 2009 at 4:06 PM, Garry Saddington
mailto:ga...@schoolteachers.co.uk>> wrote:
I ahve the following python file that I am running as an external
method in Zope.
def backup():
import os
os.popen("c:/scholarpack/postgres/bin/pg_dump scholarpack >
c:/scholarpack/ancillary/scholarpack.sql")
Have you tried running that command on the command line by itself
(as the same user that runs the phython)? If that gives you the same
result, then you know for sure that it's a function of the pg_dump
options and not the python script.
Are you looking for the full SQL of the scholarpack database?
Yes
What user is this running as?
scholarpack but no OS user scholarpack, but why should it create a file
then not fill the contents?
Remember, in your case, pg_dump is going to try to connect as the OS
username running your script. you may want to include the username
option to pg_dump:
pg_dump -U scholarpack
Zope is running as user scholarpack and I have tried the above as -U
scholarpack and it works
Try running that on the commandline first, by itself, as the same
user that runs the python script.
Looks like the script is at fault
regards
Garry
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] running pg_dump from python
On Sun, Jun 14, 2009 at 4:06 PM, Garry Saddington <
ga...@schoolteachers.co.uk> wrote:
> I ahve the following python file that I am running as an external method in
> Zope.
>
> def backup():
> import os
> os.popen("c:/scholarpack/postgres/bin/pg_dump scholarpack >
> c:/scholarpack/ancillary/scholarpack.sql")
Have you tried running that command on the command line by itself (as
the same user that runs the phython)? If that gives you the same result,
then you know for sure that it's a function of the pg_dump options and not
the python script.
Are you looking for the full SQL of the scholarpack database? What user
is this running as? Remember, in your case, pg_dump is going to try to
connect as the OS username running your script. you may want to include the
username option to pg_dump:
pg_dump -U scholarpack
Try running that on the commandline first, by itself, as the same user
that runs the python script. If it works, then you know for sure that any
problems from here on out are just a function of the python script and not
pg_dump itself.
--Scott
[GENERAL] running pg_dump from python
I ahve the following python file that I am running as an external method
in Zope.
def backup():
import os
os.popen("c:/scholarpack/postgres/bin/pg_dump scholarpack >
c:/scholarpack/ancillary/scholarpack.sql")
data=open('c:/scholarpack/ancillary/scholarpack.sql','r')
r=data.read()
data.close
return r
However, when I run this script it creates the file scholarpack.sql but
the file is empty.
Any help much appreciated.
Regards
Garry
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cygwin and postgresql
Found it. The cygwin executables for postgres are installed under /usr/sbin, which is NOT in the PATH thus the problem, and the obvious solution. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cygwin and postgresql
On Sun, June 14, 2009 15:45, Scott Marlowe wrote: why it would not form part of the cygwin >> installation? > > Is there a reason you're not using the native windows postgresql > packages? > Because for some reason, processes running in the cygwin environment could not create databases in the postgresql instance running in windows. As I develop for Linux and use cygwin to (mostly) replicate that environment on my MS-Win laptop I thought that moving everything having to do with the development environment under cygwin would be best. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cygwin and postgresql
On Sun, Jun 14, 2009 at 11:30 AM, James B. Byrne wrote: > This may not be the right place for this question but I am going to > start here anyway. > > I have installed postgresql in cygwin on a MS-WinXPpro system. I > wish to initialise a database instance. However, I cannot find any > program file called initdb. In /usr/bin I can see files like: > pg_config.exe, pg_dump.exe and pg_dumpall.exe but no initdb.exe > anywhere. Is there something about initdb that I do not understand > or some reason why it would not form part of the cygwin > installation? Is there a reason you're not using the native windows postgresql packages? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cygwin and postgresql
"James B. Byrne" writes: > The packages that cygwin says I have installed are: > 8.2.11-1 postgresql: PostgreSQL Data Base Management System 3,707k > 8.2.11-1 postgresql-client: Front-end programs for PostgreSQL 8.x 972k > 8.2.11-1 postgresql-contrib ... 373k > 8.2.11-1 postgresql-devel: ... server side programs 488k > 8.2.11-1 postgresql-doc > 8.2.11-1 postgresql-plperl > and that is all. > There is not a package listed with "-server" in it. Well, I don't know whose package set this is, but I'd bet there was supposed to be a -server package there too. That core package looks too small to include the server. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cygwin and postgresql
On Sun, June 14, 2009 13:34, Tom Lane wrote: > > That sounds suspiciously like a client-only installation. What > package did you install exactly? Was there a -server package > beside it? The packages that cygwin says I have installed are: 8.2.11-1 postgresql: PostgreSQL Data Base Management System 3,707k 8.2.11-1 postgresql-client: Front-end programs for PostgreSQL 8.x 972k 8.2.11-1 postgresql-contrib ... 373k 8.2.11-1 postgresql-devel: ... server side programs 488k 8.2.11-1 postgresql-doc 8.2.11-1 postgresql-plperl and that is all. There is not a package listed with "-server" in it. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cygwin and postgresql
"James B. Byrne" writes: > I have installed postgresql in cygwin on a MS-WinXPpro system. I > wish to initialise a database instance. However, I cannot find any > program file called initdb. In /usr/bin I can see files like: > pg_config.exe, pg_dump.exe and pg_dumpall.exe but no initdb.exe > anywhere. Is there something about initdb that I do not understand > or some reason why it would not form part of the cygwin > installation? That sounds suspiciously like a client-only installation. What package did you install exactly? Was there a -server package beside it? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] cygwin and postgresql
This may not be the right place for this question but I am going to start here anyway. I have installed postgresql in cygwin on a MS-WinXPpro system. I wish to initialise a database instance. However, I cannot find any program file called initdb. In /usr/bin I can see files like: pg_config.exe, pg_dump.exe and pg_dumpall.exe but no initdb.exe anywhere. Is there something about initdb that I do not understand or some reason why it would not form part of the cygwin installation? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] help with data recovery from injected UPDATE
Merlin Moncure wrote: >> postgresql 8.1 supports pitr archiving. you can >> do continuous backups and restore the database to just before the bad >> data. I tried using point-in-time-recovery to restore the state of the database immediately before the corruption. It didn't work, but it was quite a show. Here's the story. After much wailing and gnashing of teeth, I got postmaster to execute a recovery (so that recovery.conf was renamed to recovery.done). But the database was completely screwed after the recovery. Here's an example of the kind of output I saw while executing a simple SELECT statement: postgres=# SELECT entry_date,machine_id,coin FROM collections WHERE entry_date::date>'2009-06-06' ORDER BY entry_date; WARNING: could not write block 32 of 1663/10793/2608 DETAIL: Multiple failures --- write error may be permanent. ERROR: xlog flush request 0/4DC6CC88 is not satisfied --- flushed only to 0/4DC06180 CONTEXT: writing block 32 of relation 1663/10793/2608 Here's the recovery procedure I followed: 0. Shortly after the corruption on June 9, 2009, I shut down the server and backed up the entire data directory. The recovery procedure described herein begins with this file system backup. 1. The most recent non-corrupted snapshot of the database is a pg_dump from May 13, 2009. (I don't have any file system backups from before the corruption.) I restored the database to this snapshot by executing the commands from the May 13 pg_dump on the June 9 corrupted data. 2. I removed the files in the pg_xlog directory and replaced them with the contents of pg_xlog from the corrupted file system backup from June 9. 3. I modified the sample recovery.conf file so as to replay all the transactions right up until the point of corruption. The hope was that postmaster would somehow know to begin replaying transactions at the appropriate point from the May 13 state. I guess it's too much to ask postmaster to do a PITR from a pg_dump backup, as opposed to a file system backup. Bummer. By the way, I can reliably get postmaster to hang during startup if I manually create the pg_xlog\RECOVERYHISTORY and pg_xlog\RECOVERYXLOG directories (even with correct permissions) before starting up the server. When I say "hang", I mean that (i) any attempt to connect is met with the response "FATAL: the database system is starting up", and (ii) "pg_ctl stop" cannot be used to shut down the server -- I have to use "pg_ctl kill" Anyway, I'm going to try implementing Tom's suggestion of writing a program to modify the xmin/xmax values. I expect this approach won't work, as autovacuum was on at the time of corruption. However, the files in the data directory are quite large -- many times larger than a pg_dump. The database sees such a small amount of traffic that it's possible that even vacuum decided not to bother reclaiming the unused storage created by the corrupting transaction (?). Here's hoping. -Gus On Thu, Jun 11, 2009 at 1:43 PM, Gus Gutoski wrote: > Thanks for the replies. > > Tom Lane wrote: >> This being 8.1, if you haven't turned on autovacuum there is some chance >> of that. > > Unfortunately, autovacuum was on. I don't recall ever turning it on, > but this database is over two years old; it's possible that I blindly > followed advice from pgAdmin or something way back when. > > Merlin Moncure wrote: >> does postgresql 8.1 have last_vacuum time in the pg_stat_all_tables >> coiumn? > > I did not see a column called last_vacuum in the pg_stat_all_tables table. > >> postgresql 8.1 supports pitr archiving. you can >> do continuous backups and restore the database to just before the bad >> data. > > I'm learning about this now. I'm cautiously optimistic, as my pg_xlog > directory contains some files whose timestamp is near the time of the > 'incident'. > > By "backup" do you mean the contents of a pg_dump? The most recent > dump was two months ago. I'm worried that my log files might not go > far enough back in time to restore the table from the most recent > dump. > > Both Tom's and Merlin's suggestions carry a significant learning > curve. I'll do what I can in the coming days and post to the list if > anything noteworthy happens. > >> plus, there is no way you are escaping the obligatory 'where are your >> backups?'. :-). > > It's a classic story. I'm volunteering about one day per month for > this project, learning SQL as I go. Priority was always given to the > "get it working" tasks and never the "make it safe" tasks. I had/have > grandiose plans to rewrite the whole system properly after I graduate. > Unfortunately, the inevitable corruption didn't wait that long. > > Cheers. > > -Gus > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Opinions on how to Integrate Transactions
Hey, I'm new to using transactions. I'd like to integrate them into my PHP application to prevent a few issues we've been having. Such as, some queries in the beginning of the php script running, then an error occurring, preventing queries toward the bottom of the script from running. It's leaving me with things such as a forum topic without any associated forum posts. From what I've read on transactions, they would be the solution to my problems. So I have a couple questions about them. 1. Is there a way to list active transactions on all databases? I'd like a way to see if there are any "idle" transactions that are just hanging there. Is there a way to "kill" them from the console? 2. I have a PHP class which communicates with the DB, which is included into every other page on the site. Should I just add BEGIN and COMMIT at the beginning and end of the class? This way, I wont need to edit every single page on my site to include BEGIN and COMMIT? Or will that type of "blind" transactions cause problems? 3. What happens when there IS an error? Do I need to run ROLLBACK every time? Or will it automatically reset when the connection is closed when the PHP script dies? Thanks for the advice. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] terminating connection because of crash of another server process
sergio nogueira writes: > then i kill the process executing the truncate. > the result: > LOG: server process (PID 3421) was terminated by signal 9: > Killed > Any idea on how to prevent this? Don't do that. kill -9 is *never* the recommended way to terminate a query. Next time try kill -INT, if you must do something like this. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] terminating connection because of crash of another server process
Hi, all. i have two tables (teste1 and teste2). i was inserting one million rows on each by a script PHP. In another process, i have created a view (ct1t2) to count rows on the tables and started it then i killed the script (killing the apache server) and in another process i've executed the command 'truncate table teste1' the both process (select - by the view and truncate) get locked by a long time. then i kill the process executing the truncate. the result: STATEMENT: create view ct1t2 as select count(t1.id_linha) as ct1, count(t2.id_linha) as ct2 from teste1 t1, teste2 t2; LOG: unexpected EOF on client connection <-- when i killed apache LOG: server process (PID 3421) was terminated by signal 9: Killed <-- when i killed de truncate LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing FATAL: the database system is in recovery mode FATAL: the database system is in recovery mode LOG: database system was interrupted; last known up at 2009-06-14 11:25:32 BRT LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 3/FC97C88C LOG: redo is not required LOG: autovacuum launcher started LOG: database system is ready to accept connections Any idea on how to prevent this? i'm using PostgreSQL 8.3.6 on Fedora10 Sorry by my english. I hope you can understand ... Att., Sergio Nogueira
Re: [GENERAL] Escaping regular expressions in plperl
Toomas Vendelin writes: > In the following chunk of PlPerl code the date doesn't match the regex: > my $endby = '2009-06-13'; > my $t = spi_exec_query(qq|SELECT CASE WHEN '$endby' ~ E'.*\\d\\d\\d\\d > \\-\\d\\d?\\-\\d\\d?.*' > THEN '$endby'::timestamptz ELSE CURRENT_DATE::timestamptz END AS > test|); Backslash is an active escape character in qq constants, no? You'd need to double all those backslashes, or choose a different quoting method for the query. (And I hope this is all encased in dollar quoting for the function body...) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [sfpug] [GENERAL] "Rails and PostgreSQL" now up on media.postgresql.org
On Thursday 11 June 2009 21:48:22 Dave Page wrote: > Can you embed the files on the PUGs page like JD suggests? What I want > to avoid is an ad-hoc website springing up on media.postgresql.org > that ends up in Google and being linked from who-knows-where. While there is a point to that, having the entry points spread around the net doesn't exactly make it easier to find and use the content. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] citext.so
Does anyone have citext.so compiled for postgresql 8.2.* 64 bit? I can't find it anywhere and we are compiling binary, so I can't compile it without downloading the source... Thank you Sim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] citext.so
> I can't find it anywhere and we are *compiling* binary, so I can't compile > it without downloading the source... I mean we are installing binary -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Escaping regular expressions in plperl
How should one escape regular expressions in plperl?
In the following chunk of PlPerl code the date doesn't match the regex:
my $endby = '2009-06-13';
my $t = spi_exec_query(qq|SELECT CASE WHEN '$endby' ~ E'.*\\d\\d\\d\\d
\\-\\d\\d?\\-\\d\\d?.*'
THEN '$endby'::timestamptz ELSE CURRENT_DATE::timestamptz END AS
test|);
die "$t->{rows}[0]{test}";
However, when I run the following query using PgAdmin, the date DOES
match the regex.
SELECT CASE WHEN '$endby' ~ E'.*\\d\\d\\d\\d\\-\\d\\d?\\-\\d\\d?.*'
THEN '$endby'::timestamptz ELSE CURRENT_DATE::timestamptz END AS
test
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
