Re: [GENERAL] trigger help
Michael ,list You are you are right, thanks a lot for your help and tinme. best regards MDC --- Michael Fuhr <[EMAIL PROTECTED]> escribió: > On Tue, Aug 22, 2006 at 02:37:19PM -0300, marcelo > Cortez wrote: > > > This isn't a trigger function. Are you sure > "trigger" is the > > > word you meant? > > > > yes i do > > I see: the function you originally posted is called > by a trigger > function. In any case the answer is the same: > functions can't start > or end transactions because they're already being > executed in the > context of an outer transaction. My previous > message mentioned > using dblink as a way around that, but that's not > necessarily good > design -- one problem is that if the outer > transaction rolls back > then transactions that have already been committed > over a dblink > connection won't be rolled back. Doing transaction > control from > outside the functions would probably be better. > > -- > Michael Fuhr > > ---(end of > broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] UUID as primary key
# [EMAIL PROTECTED] / 2006-08-21 21:16:14 -0700: > I'm considering using a UUID as a primary / foreign key for my schema, > to help ensure portability of data in a multi-master context. Does > anyone have experience with this? > > There's a project on Gborg (pguuid) to create a "native" UUID type, but > it looks stagnant (and I'd prefer to use PostgreSQL out of the box, if I > can). So I'm considering three possible representations: > > * VARCHAR(36) or CHAR(36) containing the standard textual encoding > * NUMERIC(40,0) containing the 128-bit binary version of the UUID, > considered as an integer > * A pair of BIGINT columns, containing the two 64-bit halves of the > binary UUID, handled together as a two-column key. > > Would any of these give reasonable performance (for joins of large > tables), compared to int4 IDs? Is any of them clearly any better or > worse than the others? Ralf Engelschall's OSSP uuid looks very good. Written in C with interfaces into PostgreSQL, PHP and C++ (classes wrapping the C structures and functions). http://www.ossp.org/pkg/lib/uuid/ You should be able to e. g. CREATE TABLE t (id UUID DEFAULT 'uuid(4)' PRIMARY KEY); -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Queries joining views
Tom Lane wrote: Alban Hertroys <[EMAIL PROTECTED]> writes: I'm confused too. Would it be possible for you to send me a dump of your database? Attached is a cleaned out database, the full schema is included, but only the relevant tables contain any data. Thanks. After digging through it a bit, I understand what's happening, but I'm not seeing any simple fix. The problem is basically that you've got create or replace view mm_product as SELECT ... FROM mm_product_table JOIN mm_object mmop USING (number); and then the problem query has WHERE mm_product.number = insrel.snumber which causes the planner to conclude that mm_product_table.number, mm_object.number, and mm_insrel_table.snumber are all basically interchangeable. In particular it ends up performing the join between mm_product_table.number and mm_object.number as though mm_product_table.number were being joined to mm_insrel_table.snumber. It's even worse, I guess, as the mm_insrel view joins mm_insrel_table with mm_object again. So basically the query performs a self-join on mm_object with a detour through mm_insrel_table and mm_product_table... Which is fine, except that it's thinking that the statistics for mm_object.number are applicable in this context, and they're completely misleading. After the join to mm_insrel_table, the statistics of the variable are really like mm_insrel_table.number --- in particular the fraction of the table that has to be visited is much larger than it would've been for mm_object as a whole. I don't entirely understand what you're saying here. Mm_object is always larger than any other table in the database, as every table joins with (different) records in it to determine it's otype and owner. So I don't understand how a fraction of any of those tables could be larger than mm_object as a whole... In fact, originally the schema used inheritance; every table inherited (directly or indirectly) from mm_object. As this resulted in unions, which caused much more performance problems than the current view-approach, I implemented the current approach. In fact, this approach was lent from what MMBase uses for the MSSQL layer. Well, as I implemented the way the views are defined, there is room for changes in that area. Suggestions are welcome. This is a problem we've understood in a generic form for awhile: a join or selection might change the statistics of a variable, and so the info stored in the catalogs ought to be modified somehow to predict what will happen at upper join levels. We've not seen it in this particular form before, though. I'm not sure if there's a whole lot you can do about it in the near term other than refactor your schema to avoid having different tables joining to different subranges of mm_object.number. (You don't necessarily have to get rid of mm_object --- just try assigning its keys from a serial, or something, so that there's no correlation to the ranges of keys in other tables.) Unfortunately the number key is required to correlate to the number keys in other tables. That's the whole point of that table. It's also already generated from a sequence... I am looking at a view options at the moment: 1.) Cluster mm_object on an index over otype - I'm not sure how that would influence the statistics; if it doesn't then this wouldn't change much. 2.) Change mm_object into a view over the tables that now join with it. I'll have to devise some way to get the otype and owner columns into the other tables. 3.) An extension to option 2; Creating seperate tables, only containing the relevant sections from mm_object, combining them into a view-version of mm_object. Like this: CREATE TABLE mm_product_object ( number integer PRIMARY KEY, otype integer, owner text ); CREATE TABLE mm_insrel_object ( number integer PRIMARY KEY, otype integer, owner text ); (I recall seeing an inheritance-like statement that makes copies of table definitions - seems useful in this case) CREATE OR REPLACE VIEW mm_object AS SELECT * FROM mm_product_object UNION ALL SELECT * FROM mm_insrel_object; It remains to be seen that MMBase can handle mm_object being a view, but (if not) it probably will work if it's an updatable view. I'm leaning to option 3, but if option 1 helps it saves me a lot of trouble. We might be able to do something about actually solving the statistical problem in 8.3, but I fear it's too late to think about it for 8.2. Well, I had hoped for a suitable workaround, and I believe I may have a few options now. Waiting for the next PostgreSQL release never really was an option for us (deadline is somewhere next week). So it doesn't really matter to us that there won't be a solution until 8.3, or maybe even later. Thanks for the help so far, glad to be able to point out an actual problem. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: +
Re: [GENERAL] What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation
On 18/08/06, Tom Lane <[EMAIL PROTECTED]> wrote: "Magnus Hagander" <[EMAIL PROTECTED]> writes: > No, it's a work of a simplistic perlscript IIRC. It simply looked for > the first match it could find, based on the list found in the registry > (the whole concept is a bit of an ugly hack, but it's the best we could > come up with). If there is a more fitting timezone for it, it should be > changed. I guess the question is whether, when Windows is using this setting, it tracks British summer time rules or not. Would someone check? regards, tom lane What would a reasonable check be? I can start the Windows command prompt and type "time /t" which gives me the current local time (adjusted for daylight savings). In the Windows Date/Time dialogue there is a "Automatically adjust clock for daylight saving changes" checkbox, which is checked. I don't know what registry setting this maps to, though. Alistair ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [8.1.4] Create index on timestamp fails
On Aug 22, 2006, at 5:35 PM, Tom Lane wrote:
"Arturo Perez" <[EMAIL PROTECTED]> writes:
I have a table with an column:
entry_date | timestamp with time zone| not null
And when I try to create an index on it like so:
create index entry_date_idx on =
user_tracking(date_part('year',entry_date));
I get a
ERROR: functions in index expression must be marked IMMUTABLE
According to the mailing lists, this has been working since 7.4.
I seriously doubt that. date_part on a timestamptz is stable, not
immutable, and AFAICT has been marked that way since 7.3. The problem
is that the results depend on your current TimeZone setting --- for
instance, 2AM 2006-01-01 in London is 9PM 2005-12-31 where I live.
If you only need day precision, try storing entry_date as a date
instead
of a timestamptz. Or perhaps consider timestamp without tz. But you
need something that's not timezone-dependent to make this work.
regards, tom lane
Ah, I knew it was something I was overlooking. Thanks a ton. We need
sub-day granularity (it's for a sort of weblog). Without a TZ sounds
llke
a winner.
Thanks again,
arturo
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] error msg when pg_restore
hi, there, tom. thank you for your help. i turned -c off and now i find a less no. of errors, however, i still get them! (i used to have approx 70 'errors ignored on restore', now i have approx 30 or even less than that). one of errors is like below; pg_restore: [archiver (db)] Error from TOC entry 18; 1255 17234 FUNCTION relatio n_size(text) postgres pg_restore: [archiver (db)] could not execute query: ERROR: could not access fi le "$libdir/dbsize": No such file or directory Command was: CREATE FUNCTION relation_size(text) RETURNS bigint AS '$libdir/dbsize', 'relation_size' LANGUAGE c STRICT; the options i am currently using are -i (ignore-version), -h (host), -d (database), -U (user) and -W (password). is there any way you can fix this problem? thanks, nuno Tom Lane wrote: > "nuno" <[EMAIL PROTECTED]> writes: > > when i restore a database (using a backup file), it shows me some error > > messages which do not seem to matter at all (there is no data lost in > > the database, and maybe they're just warning messages...). > > > pg_restore: [archiver (db)] could not execute query: ERROR: function > > public.plp > > gsql_validator(oid) does not exist > > Command was: DROP FUNCTION public.plpgsql_validator(oid); > > Apparently you're using the -c option when there's not really anything > that needs dropping. -c is for removing existing copies of the database > objects you're restoring. > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [8.1.4] Create index on timestamp fails
Hi
Chris,
user_tracking is not a function, it's the name of the table containing
the column entry_date. Is my syntax that far off?!
-arturo
-Original Message-From: Chris Hoover
[mailto:[EMAIL PROTECTED]Sent: Tuesday, August 22, 2006 3:02
PMTo: Arturo PerezCc:
pgsql-general@postgresql.orgSubject: Re: [GENERAL] [8.1.4] Create
index on timestamp failsIt appears that 8.1 is stricter
on checking the type of function. Look at your user_tracking
function. It is probably set as volatile. You need to change it to
be immutable.This should fix the issue.Chris
On 8/21/06, Arturo
Perez <[EMAIL PROTECTED]>
wrote:
Hi all,
Using postgresql 8.1.4
I have a table with an column:
entry_date | timestamp with time zone| not null
And when I try to create an index on it like
so: create index entry_date_idx on
user_tracking(date_part('year',entry_date));
I get a
ERROR: functions in index _expression_ must be marked IMMUTABLE
According to the mailing lists, this has been
working since 7.4. What am I doing wrong?
tia, arturo
[GENERAL] How do i store arbitrary questions and answers in SQL?
Dear group, I have been bugged by this problem on and off for years, and would like to put it out to general discussion. I suspect it is a common SQL problem but I have never found a satisfactory answer - maybe there is not one. The problem : to store, in SQL/RDBMS, an arbitrary set of questions and their answers, where the questions are text (obviously) but the answers can be dates, text, integers, money etc. think of it as a big questionnaire where at design time we have no idea what the questions will be. My usual solution to this problem is to store everything in varchar and flag the type, converting as I extract data. It is not a desirable solution. i.e. CREATE TABLE Qu (ID INT, Question VARCHAR(64)) CREATE TABLE Answer(ID INT, questionID INT, answer_text VARCHAR(255), datatype INT) Are there any other solutions out there? cheers PS I think I am essentially looking for a database that stores a datatype of VARIANT (for those of MS VB background). However my (patchy) knowledge of RDBMS internals says this is essentially breaks all the rules for optimisation and storage so is never offered. I can think of several ideas but they all fall short in some way 0. (current one) I have to cast any search term to string first but after that the SQL performs as one expects. 1. store the serialised object in binary form. (how does one search this? Totally dependant on choice of middleware language) 2. Store different types in diff columns table answer (questionID, ans_text VARCHAR, ans_money MONEY, ans_int INT But this makes searching through SQL even harder than casting, as in each query i must what answer to expect. 3. Different answer tables each with answer types - same problem as 2 but even harder. 4. I suspect pl/python might be useful, but I cannot see how at the moment... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Installation Help needed
Folks. I installed postgresql in my RHEL Server. I do a ssh login to server and start it. but the "nmap localhost" does not show the postgresql port 5432 But the server is running and as the "postgres" user i can user the server. I have created a normal user for postgres and working fine. I can not access the postgresql server from the client machines. I want the users should not login to server to access postgresql server. "nmap " from any client dows not show the 5432 port. No firewall is running in te server. How to remote access the postgresql server? regards, T.shrinivasan. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] error msg when pg_restore
by the way, i had 3 different types of errors messages; 1. pg_restore: [archiver (db)] Error from TOC entry 17; 1255 17233 FUNCTION database_size(name)* postgres 2. pg_restore: [archiver (db)] could not execute query: ERROR: could not access file "$libdir/dbsize"**: No such file or directory... 3. pg_restore: [archiver (db)] could not execute query: ERROR: function public.database_size(name)*** does not exist... * or pg_database_size(oid), pg_dir_ls(text, boolean), pg_file_read(text, bigint, bigint), pg_file_stat(text), etc. ** or "$libdir/admin" *** or public.pg_database_size(oid), public.pg_dir_ls(text, boolean), etc. every single message is one of the types specified above. i read the posts about pg_restore, and i still haven't been able to figure it out. any clue anyone? nuno wrote: > hi, there, tom. thank you for your help. > > i turned -c off and now i find a less no. of errors, however, i still > get them! > (i used to have approx 70 'errors ignored on restore', now i have > approx 30 or even less than that). > > one of errors is like below; > > pg_restore: [archiver (db)] Error from TOC entry 18; 1255 17234 > FUNCTION relatio > n_size(text) postgres > pg_restore: [archiver (db)] could not execute query: ERROR: could not > access fi > le "$libdir/dbsize": No such file or directory > Command was: CREATE FUNCTION relation_size(text) RETURNS bigint > AS '$libdir/dbsize', 'relation_size' > LANGUAGE c STRICT; > > the options i am currently using are -i (ignore-version), -h (host), -d > (database), -U (user) and -W (password). > > is there any way you can fix this problem? > > thanks, nuno > > Tom Lane wrote: > > "nuno" <[EMAIL PROTECTED]> writes: > > > when i restore a database (using a backup file), it shows me some error > > > messages which do not seem to matter at all (there is no data lost in > > > the database, and maybe they're just warning messages...). > > > > > pg_restore: [archiver (db)] could not execute query: ERROR: function > > > public.plp > > > gsql_validator(oid) does not exist > > > Command was: DROP FUNCTION public.plpgsql_validator(oid); > > > > Apparently you're using the -c option when there's not really anything > > that needs dropping. -c is for removing existing copies of the database > > objects you're restoring. > > > > regards, tom lane > > > > ---(end of broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation
"Alistair Bayley" <[EMAIL PROTECTED]> writes: > On 18/08/06, Tom Lane <[EMAIL PROTECTED]> wrote: >> I guess the question is whether, when Windows is using this setting, >> it tracks British summer time rules or not. Would someone check? > What would a reasonable check be? I can start the Windows command > prompt and type "time /t" which gives me the current local time > (adjusted for daylight savings). In the Windows Date/Time dialogue > there is a "Automatically adjust clock for daylight saving changes" > checkbox, which is checked. I don't know what registry setting this > maps to, though. Hm. It kinda sounds like you might get true GMT if that box is not checked, and the equivalent of Europe/London if it is checked. I have a vague recollection that we discussed this before and determined that there's no direct way for a program to find out if that box is checked though? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [8.1.4] Create index on timestamp fails
Arturo Perez wrote: > > On Aug 22, 2006, at 5:35 PM, Tom Lane wrote: > >I seriously doubt that. date_part on a timestamptz is stable, not > >immutable, and AFAICT has been marked that way since 7.3. The problem > >is that the results depend on your current TimeZone setting --- for > >instance, 2AM 2006-01-01 in London is 9PM 2005-12-31 where I live. > > > >If you only need day precision, try storing entry_date as a date > >instead > >of a timestamptz. Or perhaps consider timestamp without tz. But you > >need something that's not timezone-dependent to make this work. > > Ah, I knew it was something I was overlooking. Thanks a ton. We need > sub-day granularity (it's for a sort of weblog). Without a TZ sounds > llke a winner. Another idea would be to separate the date column (which would have the index) from the time column (which would have the timezone). The timezone is important -- if you have "bloggers" from all around the world you're gonna have serious problems with the archived time. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How do i store arbitrary questions and answers in SQL?
Hi, On Wed, Aug 23, 2006 at 02:40:29AM -0700, lifeisgood wrote: > CREATE TABLE Answer(ID INT, questionID INT, answer_text VARCHAR(255), > datatype INT) You can actually store answer_text as a BLOB in the database that is packed in any format your application can handle. You can serialize a class that represents the answer you need and store it, for example. -HTH -- Enver signature.asc Description: Digital signature
Re: [GENERAL] What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane > Sent: 23 August 2006 14:05 > To: Alistair Bayley > Cc: Magnus Hagander; pgsql-general@postgresql.org > Subject: Re: [GENERAL] What's special about 1916-10-01 > 02:25:20? Odd jump in internal timestamptz representation > > I have a vague recollection that we discussed this before and > determined > that there's no direct way for a program to find out if that box is > checked though? That particular setting is a DWORD registry key: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\ DisableAutoDaylightTimeSet 0 (or non-existant) means the box is checked, 1 when it is cleared. Regards, Dave. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Installation Help needed
am Wed, dem 23.08.2006, um 2:55:37 -0700 mailte shrini folgendes: > Folks. > I installed postgresql in my RHEL Server. Which version? > "nmap " from any client dows not show the 5432 port. > No firewall is running in te server. > > How to remote access the postgresql server? Edit your postgresql.conf and enable TCP/IP (port = 5432) and check your pg_hba.conf, this files are rich with comments. HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net eMail schreiben kann jeder -- lernen: http://webserv/email/email.html ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How do i store arbitrary questions and answers in SQL?
On Wed, Aug 23, 2006 at 04:13:24PM +0300, Enver ALTIN wrote: > > CREATE TABLE Answer(ID INT, questionID INT, answer_text VARCHAR(255), > > datatype INT) > > You can actually store answer_text as a BLOB in the database that is > packed in any format your application can handle. You can serialize a > class that represents the answer you need and store it, for example. There was an article on that on Elein's General Bits not too long ago. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Queries joining views
Alban Hertroys <[EMAIL PROTECTED]> writes: > Mm_object is always larger than any other table in the database, as > every table joins with (different) records in it to determine it's otype > and owner. So I don't understand how a fraction of any of those tables > could be larger than mm_object as a whole... No, I said a larger fraction, not a larger absolute number of tuples. The problem is that because mm_product contains only very small values of "number", a mergejoin looks like a great way to join it to mm_object: only the first 5% of mm_object will need to be scanned. The bug consists in applying that 5% number to mm_insrel, for which it's not correct. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [8.1.4] Create index on timestamp fails
On Wed, Aug 23, 2006 at 09:07:35AM -0400, Alvaro Herrera wrote: > Another idea would be to separate the date column (which would have the > index) from the time column (which would have the timezone). The > timezone is important -- if you have "bloggers" from all around the > world you're gonna have serious problems with the archived time. Would that indeed work ? I mean, depending on the time zone the *date* might be different by +/-1, too ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pl/R problem
Michael Fuhr wrote:
On Tue, Aug 22, 2006 at 04:24:46PM -0500, Don Isgitt wrote:
gds2=# create function sd(_float8) returns float as '' language 'plr';
CREATE FUNCTION
gds2=# select round(sd('{1.23,1.31,1.42,1.27}'::_float8)::numeric,8);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Relevant server log piece:
cannot find system Renviron
Fatal error: unable to open the base package
LOG: server process (PID 6792) exited with exit code 2
I can reproduce this on Solaris 9 with PL/R 0.6.2-alpha and R 2.2.1
if I rename $R_HOME/etc/Renviron to something else. I don't get
the "unable to open the base package" error but I do get "cannot
find system Renviron" and several other errors like "Error in
options(...) : invalid editor parameter" followed by a segmentation
fault.
So, the question: what am I missing that enables it to find the "Renviron"
Does the file $R_HOME/etc/Renviron or $R_HOME/etc//Renviron
exist and have permissions such that the PostgreSQL server can read
it? The error "cannot find system Renviron" comes from the R
function process_system_Renviron() in src/main/Renviron.c in response
to process_Renviron() returning 0, which it does if its filename
argument is NULL or if it can't open that file.
Hi Michael,
Thanks for the reply and the research. The relevant file is
[EMAIL PROTECTED] R-2.3.0]$ ls -l $R_HOME/etc/Renviron
-rw-rw-r--1 djisgitt djisgitt 1151 Jun 1 11:42
/home/djisgitt/R-2.3.0/etc/Renviron
so I don't see that as the problem.
I use R quite a lot, so I know the installation of R itself is good, but
this is the first time I have tried pl/R. Also, pgsql works, so I assume
there is something incorrect between pgsql and R. I just don't know what!
If you have any more suggestions, or need more information, let me know.
Don
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] share library version problems
Tom Lane wrote: If the Fedora 5 RPMs won't install on your FC4 machine, grab the FC5 SRPM and do a quick rpmbuild --rebuild to make custom RPMs for your environment. After about 5 minutes of compiling I get this: == pg_regress: initdb failed Examine ./log/initdb.log for the reason. make: *** [check] Error 2 error: Bad exit status from /var/tmp/rpm-tmp.67109 (%build) RPM build errors: Bad exit status from /var/tmp/rpm-tmp.67109 (%build) == /usr/src/redhat/BUILD/postgresql-8.1.4/ src/test/regress/log/initdb.log Running in noclean mode. Mistakes will not be cleaned up. initdb: cannot be run as root Please log in (using, e.g., "su") as the (unprivileged) user that will own the server process. -- Bryan White, ArcaMax Publishing Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [8.1.4] Create index on timestamp fails
Karsten Hilbert <[EMAIL PROTECTED]> writes:
> On Wed, Aug 23, 2006 at 09:07:35AM -0400, Alvaro Herrera wrote:
>> Another idea would be to separate the date column (which would have the
>> index) from the time column (which would have the timezone). The
>> timezone is important -- if you have "bloggers" from all around the
>> world you're gonna have serious problems with the archived time.
> Would that indeed work ? I mean, depending on the time zone
> the *date* might be different by +/-1, too ?
It sounds a bit bogus to me too. Another possibility is to keep the
data storage as timestamptz (which is really the recommended type for
any sort of real time values), and define the index on
date_part('day', entry_time AT TIME ZONE 'GMT')
(or whatever zone you choose to use for reference). However, to use the
index you'd have to spell the queries exactly like that, so the PITA
factor might be too high.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] [8.1.4] Create index on timestamp fails
On Wed, Aug 23, 2006 at 09:42:00AM -0400, Tom Lane wrote:
> It sounds a bit bogus to me too. Another possibility is to keep the
> data storage as timestamptz (which is really the recommended type for
> any sort of real time values), and define the index on
>
> date_part('day', entry_time AT TIME ZONE 'GMT')
That definitely sounds reasonable.
> (or whatever zone you choose to use for reference). However, to use the
> index you'd have to spell the queries exactly like that, so the PITA
> factor might be too high.
An SQL function "gmt_tz(timestamptz)" might help to cut down
on the fuss:
select ... from tbl where gmt_tz(tbl.a_tz) between ...;
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] pl/R problem
Don Isgitt wrote: Thanks for the reply and the research. The relevant file is [EMAIL PROTECTED] R-2.3.0]$ ls -l $R_HOME/etc/Renviron -rw-rw-r--1 djisgitt djisgitt 1151 Jun 1 11:42 /home/djisgitt/R-2.3.0/etc/Renviron so I don't see that as the problem. I use R quite a lot, so I know the installation of R itself is good, but this is the first time I have tried pl/R. Also, pgsql works, so I assume there is something incorrect between pgsql and R. I just don't know what! If you have any more suggestions, or need more information, let me know. I just got back from a business trip, and am trying to get caught up -- give me a few days and I'll see if I can help (i.e. I should be able to find time on the weekend)... Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] share library version problems
Bryan White <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> If the Fedora 5 RPMs won't install on your FC4 machine, grab the FC5 >> SRPM and do a quick rpmbuild --rebuild to make custom RPMs for your >> environment. > After about 5 minutes of compiling I get this: > initdb: cannot be run as root Don't do the rpmbuild as root. Alternatively, I believe there's a %define you can set to skip the regression test ... but that's probably not a good idea. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pl/R problem
On Wed, Aug 23, 2006 at 08:38:01AM -0500, Don Isgitt wrote: > Thanks for the reply and the research. The relevant file is > > [EMAIL PROTECTED] R-2.3.0]$ ls -l $R_HOME/etc/Renviron > -rw-rw-r--1 djisgitt djisgitt 1151 Jun 1 11:42 > /home/djisgitt/R-2.3.0/etc/Renviron > > so I don't see that as the problem. Have you checked the permissions on all of the directories in the file's path? Have you verified that PostgreSQL is using the same $R_HOME? You can check the environment with plr_environ(): SELECT * FROM plr_environ() ORDER BY name; -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Majordomo drops multi-line Subject:
I noticed that Majordomo drops the second and subsequent lines of a Subject: line in message before dispatching for some reason. It has done this for some time; I noticed it some time ago in pgsql-es-ayuda but I thought it may be a bug in my MUA. But I just saw it happened to a mail from Bruce as well. Is this fixable? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Majordomo drops multi-line Subject:
Alvaro Herrera wrote: > I noticed that Majordomo drops the second and subsequent lines of a > Subject: line in message before dispatching for some reason. It has > done this for some time; I noticed it some time ago in pgsql-es-ayuda > but I thought it may be a bug in my MUA. But I just saw it happened to > a mail from Bruce as well. Can you have multi-line subject lines? I didn't think that was possible. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pl/R problem
Adam Witney wrote:
Don Isgitt wrote:
Michael Fuhr wrote:
On Tue, Aug 22, 2006 at 04:24:46PM -0500, Don Isgitt wrote:
gds2=# create function sd(_float8) returns float as '' language 'plr';
CREATE FUNCTION
gds2=# select round(sd('{1.23,1.31,1.42,1.27}'::_float8)::numeric,8);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Relevant server log piece:
cannot find system Renviron
Fatal error: unable to open the base package
LOG: server process (PID 6792) exited with exit code 2
I can reproduce this on Solaris 9 with PL/R 0.6.2-alpha and R 2.2.1
if I rename $R_HOME/etc/Renviron to something else. I don't get
the "unable to open the base package" error but I do get "cannot
find system Renviron" and several other errors like "Error in
options(...) : invalid editor parameter" followed by a segmentation
fault.
So, the question: what am I missing that enables it to find the
"Renviron"
Does the file $R_HOME/etc/Renviron or $R_HOME/etc//Renviron
exist and have permissions such that the PostgreSQL server can read
it? The error "cannot find system Renviron" comes from the R
function process_system_Renviron() in src/main/Renviron.c in response
to process_Renviron() returning 0, which it does if its filename
argument is NULL or if it can't open that file.
Hi Michael,
Thanks for the reply and the research. The relevant file is
[EMAIL PROTECTED] R-2.3.0]$ ls -l $R_HOME/etc/Renviron
-rw-rw-r--1 djisgitt djisgitt 1151 Jun 1 11:42
/home/djisgitt/R-2.3.0/etc/Renviron
so I don't see that as the problem.
I use R quite a lot, so I know the installation of R itself is good, but
this is the first time I have tried pl/R. Also, pgsql works, so I assume
there is something incorrect between pgsql and R. I just don't know what!
If you have any more suggestions, or need more information, let me know.
Hi Don,
your functions work fine on my linux system. Just wondering, could the
fact that your R_HOME is installed under your home directory be causing
the problem? Postgesql will be running as a different user...
adam
Hi Adam,
Thanks for checking that. There is world read access to R_HOME, so I
hope that is sufficient.
Don
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Majordomo drops multi-line Subject:
Bruce Momjian wrote: > Alvaro Herrera wrote: > > I noticed that Majordomo drops the second and subsequent lines of a > > Subject: line in message before dispatching for some reason. It has > > done this for some time; I noticed it some time ago in pgsql-es-ayuda > > but I thought it may be a bug in my MUA. But I just saw it happened to > > a mail from Bruce as well. > > Can you have multi-line subject lines? I didn't think that was > possible. Yes. This is the header of a mail you sent to -patches: From: Bruce Momjian <[EMAIL PROTECTED]> To: Zdenek Kotala <[EMAIL PROTECTED]> CC: Peter Eisentraut <[EMAIL PROTECTED]>, pgsql-patches@postgresql.org Date: Wed, 23 Aug 2006 09:05:29 -0400 (EDT) Subject: Re: [PATCHES] Allow commenting of variables in Note the Subject is truncated w.r.t. the mail you were responding, which had this: From: Zdenek Kotala <[EMAIL PROTECTED]> To: Peter Eisentraut <[EMAIL PROTECTED]> Cc: pgsql-patches@postgresql.org, [EMAIL PROTECTED] Date: Wed, 23 Aug 2006 14:44:19 +0200 Subject: Re: [PATCHES] Allow commenting of variables in postgresql.conf to - See your sent-mail folder, you'll see that the message you actually sent had something like this: Subject: Re: [PATCHES] Allow commenting of variables in postgresql.conf to - What happened with the second line? What I concluded has happened, from observations on the other list, Majordomo removed it. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Slow insert/delete
I am in the process of converting an old system to a new system where I have chosen to use postgres in stead of a home grow system based on b-trees. The system receives 2650 message a total of 10Mbytes of data per 15 minutes this information have to be store in 4 tables in the database. Some of the messages are updates to existing data and the result is: 1038 records in table 1 size per record 282 bytes 2074 records in table 2 size per record 36 bytes 21488 records in table 3 size per record 60 bytes 25676 records in table 4 size per record 42 bytes. Without indexes this should be something like 2,7Mbytes/15min. The disk speed is as follows taken from hdparm -Tt /dev/hda: Timing buffer-cache reads: 128 MB in 0.89 seconds =143.82 MB/sec Timing buffered disk reads: 64 MB in 1.45 seconds = 44.14 MB/sec My problem is that the postmaster process is using between 70-90% of the CPU time. (seen using top) and the total wall time with nothing else running. It takes 6 minutes to ingest the data with an empty database and 25 minutes (wall time) if all the data is already in the database. The processing is done as follows: 1. Start transaction 2. check if message is in table 1 and if so delete records from table 1(1 row),2(2 rows),3(30 rows),4(50 rows). 3. split the message and insert the date in table 1(1 row),2(2 rows),3(30 rows),4(50 rows) 4. end transaction I need some suggestions so that I can ingest the data faster. with the same computer and disks. We are running redhat 7.2 on the server and I am using the c interface to write the data to the server. Thanks for any help, Thor. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pl/R problem
Michael Fuhr wrote: On Wed, Aug 23, 2006 at 08:38:01AM -0500, Don Isgitt wrote: Thanks for the reply and the research. The relevant file is [EMAIL PROTECTED] R-2.3.0]$ ls -l $R_HOME/etc/Renviron -rw-rw-r--1 djisgitt djisgitt 1151 Jun 1 11:42 /home/djisgitt/R-2.3.0/etc/Renviron so I don't see that as the problem. Have you checked the permissions on all of the directories in the file's path? Have you verified that PostgreSQL is using the same $R_HOME? You can check the environment with plr_environ(): Yes. At least world read all the way. gds2=# select * from plr_environ() order by name; R_HOME | /home/djisgitt/R-2.3.0 SELECT * FROM plr_environ() ORDER BY name; Thank you, Michael. I appreciate your help. Don ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pl/R problem
Don Isgitt wrote: > Michael Fuhr wrote: > >On Wed, Aug 23, 2006 at 08:38:01AM -0500, Don Isgitt wrote: > > > >>Thanks for the reply and the research. The relevant file is > >> > >>[EMAIL PROTECTED] R-2.3.0]$ ls -l $R_HOME/etc/Renviron > >>-rw-rw-r--1 djisgitt djisgitt 1151 Jun 1 11:42 > >>/home/djisgitt/R-2.3.0/etc/Renviron > >> > >>so I don't see that as the problem. > >> > > > >Have you checked the permissions on all of the directories in the > >file's path? Have you verified that PostgreSQL is using the same > >$R_HOME? You can check the environment with plr_environ(): > > > > > Yes. At least world read all the way. The best way to make sure this is the case I've found is su - postgres stat /home/djisgitt/R-2.3.0/etc/Renviron (or ls, whatever) I've seen people swearing they have world access all the way and then noticing they are missing a little bit somewhere. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Majordomo drops multi-line Subject:
On 23.08.2006, at 16:31 Uhr, Alvaro Herrera wrote: Can you have multi-line subject lines? I didn't think that was possible. Yes. This is the header of a mail you sent to -patches: Aha? Subject is an "unstructured header field" and according to RFC 2822 [1]: 8<8<8<8<8<8< 2.2.1. Unstructured Header Field Bodies Some field bodies in this standard are defined simply as "unstructured" (which is specified below as any US-ASCII characters, except for CR and LF) with no further restrictions. These are referred to as unstructured field bodies. Semantically, unstructured field bodies are simply to be treated as a single line of characters with no further processing (except for header "folding" and "unfolding" as described in section 2.2.3). 8<8<8<8<8<8< So they don't contain line feeds or carriage returns and so the can't be multi-line. If a mail client sends multi line subjects it does something against the RFC and I assume with that, it does something wrong. This is the theory in RFC 2822 as far as I understand it. cug [1] http://tools.ietf.org/html/rfc2822 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pl/R problem
On Wed, Aug 23, 2006 at 09:37:17AM -0500, Don Isgitt wrote: > Michael Fuhr wrote: > >Have you checked the permissions on all of the directories in the > >file's path? Have you verified that PostgreSQL is using the same > >$R_HOME? You can check the environment with plr_environ(): > > > Yes. At least world read all the way. Just readable or also executable? What's the output of the following? ls -ld /home/djisgitt/R-2.3.0/etc /home/djisgitt/R-2.3.0 /home/djisgitt /home -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] error msg when pg_restore
"nuno" <[EMAIL PROTECTED]> writes: > pg_restore: [archiver (db)] could not execute query: ERROR: could not > access file "$libdir/dbsize": No such file or directory > Command was: CREATE FUNCTION relation_size(text) RETURNS bigint > AS '$libdir/dbsize', 'relation_size' > LANGUAGE c STRICT; You haven't installed the "dbsize" module, which evidently existed in your old installation. [ checks around... ] Actually, dbsize disappeared from contrib in 8.1 because the functions got integrated into the core (some under different names than before). So you should be able to just ignore those errors, unless maybe some of your own objects like views depended on them. In that case you'd have to recreate the view manually with a corrected definition. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Majordomo drops multi-line Subject:
It most likely conforms strictly to href="http://tools.ietf.org/html/rfc822#page-21";>Rfc 822 which is the standard, and mostly canonical, and allows for CR and LF but not the two together (CRLF), if I'm reading it correctly: text= atoms, specials, CR & bare LF, but NOT ; comments and including CRLF> ; quoted-strings are ; NOT recognized. optional-field = / "Message-ID"":" msg-id / "Resent-Message-ID" ":" msg-id / "In-Reply-To" ":" *(phrase / msg-id) / "References"":" *(phrase / msg-id) / "Keywords" ":" #phrase / "Subject" ":" *text / "Comments" ":" *text / "Encrypted" ":" 1#2word / extension-field ; To be defined / user-defined-field ; May be pre-empted Alvaro Herrera wrote: Bruce Momjian wrote: Alvaro Herrera wrote: I noticed that Majordomo drops the second and subsequent lines of a Subject: line in message before dispatching for some reason. It has done this for some time; I noticed it some time ago in pgsql-es-ayuda but I thought it may be a bug in my MUA. But I just saw it happened to a mail from Bruce as well. Can you have multi-line subject lines? I didn't think that was possible. Yes. This is the header of a mail you sent to -patches: From: Bruce Momjian <[EMAIL PROTECTED]> To: Zdenek Kotala <[EMAIL PROTECTED]> CC: Peter Eisentraut <[EMAIL PROTECTED]>, pgsql-patches@postgresql.org Date: Wed, 23 Aug 2006 09:05:29 -0400 (EDT) Subject: Re: [PATCHES] Allow commenting of variables in Note the Subject is truncated w.r.t. the mail you were responding, which had this: From: Zdenek Kotala <[EMAIL PROTECTED]> To: Peter Eisentraut <[EMAIL PROTECTED]> Cc: pgsql-patches@postgresql.org, [EMAIL PROTECTED] Date: Wed, 23 Aug 2006 14:44:19 +0200 Subject: Re: [PATCHES] Allow commenting of variables in postgresql.conf to - See your sent-mail folder, you'll see that the message you actually sent had something like this: Subject: Re: [PATCHES] Allow commenting of variables in postgresql.conf to - What happened with the second line? What I concluded has happened, from observations on the other list, Majordomo removed it. -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Majordomo drops multi-line Subject:
Guido Neitzer wrote: > 8<8<8<8<8<8< > 2.2.1. Unstructured Header Field Bodies > > >Some field bodies in this standard are defined simply as >"unstructured" (which is specified below as any US-ASCII characters, >except for CR and LF) with no further restrictions. These are >referred to as unstructured field bodies. Semantically, > unstructured >field bodies are simply to be treated as a single line of characters >with no further processing (except for header "folding" and >"unfolding" as described in section 2.2.3). > > 8<8<8<8<8<8< So see what "folding" means. Section 2.2.3 says 2.2.3. Long Header Fields Each header field is logically a single line of characters comprising the field name, the colon, and the field body. For convenience however, and to deal with the 998/78 character limitations per line, the field body portion of a header field can be split into a multiple line representation; this is called "folding". The general rule is that wherever this standard allows for folding white space (not simply WSP characters), a CRLF may be inserted before any WSP. For example, the header field: Subject: This is a test can be represented as: Subject: This is a test -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Majordomo drops multi-line Subject:
On 23.08.2006, at 16:51 Uhr, Alvaro Herrera wrote: Guido Neitzer wrote: 8<8<8<8<8<8< 2.2.1. Unstructured Header Field Bodies Some field bodies in this standard are defined simply as "unstructured" (which is specified below as any US-ASCII characters, except for CR and LF) with no further restrictions. These are referred to as unstructured field bodies. Semantically, unstructured field bodies are simply to be treated as a single line of characters with no further processing (except for header "folding" and "unfolding" as described in section 2.2.3). 8<8<8<8<8<8< So see what "folding" means. Section 2.2.3 says 2.2.3. Long Header Fields Each header field is logically a single line of characters comprising the field name, the colon, and the field body. For convenience however, and to deal with the 998/78 character limitations per line, the field body portion of a header field can be split into a multiple line representation; this is called "folding". The general rule is that wherever this standard allows for folding white space (not simply WSP characters), a CRLF may be inserted before any WSP. For example, the header field: Interesting. Haven't seen that. Thanks for the hint. Not really intuitive, but okay. cug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Majordomo drops multi-line Subject:
Alvaro Herrera wrote: Bruce Momjian wrote: Alvaro Herrera wrote: I noticed that Majordomo drops the second and subsequent lines of a Subject: line in message before dispatching for some reason. It has done this for some time; I noticed it some time ago in pgsql-es-ayuda but I thought it may be a bug in my MUA. But I just saw it happened to a mail from Bruce as well. Can you have multi-line subject lines? I didn't think that was possible. Yes. This is the header of a mail you sent to -patches: To further this: RCPT TO: [EMAIL PROTECTED] m550 5.7.1 [EMAIL PROTECTED] Relaying denied. Proper authentication required. RCPT TO: [EMAIL PROTECTED] 250 2.1.5 [EMAIL PROTECTED] Recipient ok data 354 Enter mail, end with "." on a line by itself Subject: asdf asdfasdf hello . 250 2.0.0 k7NFEHfh005371 Message accepted for delivery RCPT TO: [EMAIL PROTECTED] 503 5.0.0 Need MAIL before RCPT MAIL FROM: [EMAIL PROTECTED] 250 2.1.0 [EMAIL PROTECTED] Sender ok RCPT TO: [EMAIL PROTECTED] 250 2.1.5 [EMAIL PROTECTED] Recipient ok data 354 Enter mail, end with "." on a line by itself Subject: asdfasdfasdf asdfasdfasdfaasdffasdfasdasdf this is a test . 250 2.0.0 k7NFEHfi005371 Message accepted for delivery Both of these came through with proper multi line subjects. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] share library version problems
Tom Lane wrote: Don't do the rpmbuild as root. Alternatively, I believe there's a %define you can set to skip the regression test ... but that's probably not a good idea. I think I have it solved now. I am not to familiar with the process of building from source RPMs. You said to not do it as root but that meant I did not have write access to /usr/src/redhat. I tried to options to build from a different location without much luck. In the end I moved /usr/src/redhat to /usr/src/redhat.old and created a new one (including sub-directories) and made myself the owner. It then builds fine. Seems like there has to be an easier way. Anyway, after installing the new RPMs on my FC4 dev server and rebuilding my programs, the programs do now run on my web server (stock FC4 PostgreSQL). Thanks for your help -- Bryan White, ArcaMax Publishing Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Majordomo drops multi-line Subject:
Guido Neitzer wrote: So they don't contain line feeds or carriage returns and so the can't be multi-line. If a mail client sends multi line subjects it does something against the RFC and I assume with that, it does something wrong. This is the theory in RFC 2822 as far as I understand it. I think he referred to a long subject line being "folded" as per section 2.2.3 of rfc2822. -- bkw ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Majordomo drops multi-line Subject:
Alvaro Herrera <[EMAIL PROTECTED]> writes: > I noticed that Majordomo drops the second and subsequent lines of a > Subject: line in message before dispatching for some reason. It has > done this for some time; I noticed it some time ago in pgsql-es-ayuda > but I thought it may be a bug in my MUA. But I just saw it happened to > a mail from Bruce as well. > Is this fixable? Even though multi-line Subject: is theoretically legal according to the RFCs, it's certainly an awful idea; how many MUAs do you know that provide more than one line to display the subject in a normal view? So I don't really care if Majordomo truncates the subject --- I wouldn't see the rest of it anyway. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] share library version problems
Bryan White <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Don't do the rpmbuild as root. > I think I have it solved now. I am not to familiar with the process of > building from source RPMs. You said to not do it as root but that meant > I did not have write access to /usr/src/redhat. Well, nobody at Red Hat builds RPMs in /usr/src/redhat anymore ;-). The setup I use involves creating a directory ~/rpmwork, making these subdirectories in it: BUILD/RPMS/ SOURCES/ SPECS/SRPMS/ and making a file ~/.rpmmacros containing just %_topdir /home/tgl/rpmwork (adjust to suit where your work dir actually is of course). To build, copy the SRPM into the SRPMS subdirectory, cd there, and go rpmbuild --rebuild srpmfilename The only part you need root for is actually installing the built RPMs (which end up under the RPMS directory of course). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Majordomo drops multi-line Subject:
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > I noticed that Majordomo drops the second and subsequent lines of a > > Subject: line in message before dispatching for some reason. It has > > done this for some time; I noticed it some time ago in pgsql-es-ayuda > > but I thought it may be a bug in my MUA. But I just saw it happened to > > a mail from Bruce as well. > > > Is this fixable? > > Even though multi-line Subject: is theoretically legal according to the > RFCs, it's certainly an awful idea; how many MUAs do you know that > provide more than one line to display the subject in a normal view? > So I don't really care if Majordomo truncates the subject --- I wouldn't > see the rest of it anyway. Huh, but the MUA auto-unfolds it for view. Both mutt and Elm do that fine -- the folding and unfolding. I would think exmh is pretty thoroughly broken if it didn't. We can do an experiment and send you a Cc'ed message through the list and a copy to you directly. The direct copy should have the full subject, and the list one would be truncated. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Majordomo drops multi-line Subject:
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Even though multi-line Subject: is theoretically legal according to the >> RFCs, it's certainly an awful idea; how many MUAs do you know that >> provide more than one line to display the subject in a normal view? >> So I don't really care if Majordomo truncates the subject --- I wouldn't >> see the rest of it anyway. > Huh, but the MUA auto-unfolds it for view. Both mutt and Elm do that > fine -- the folding and unfolding. I would think exmh is pretty > thoroughly broken if it didn't. Well, if I actually choose to read the message, sure I'll see all of it. The point here is that you've got one line (and only about 50 characters at that) to get my attention, and so I'm perfectly fine with list software that, erm, strongly encourages brevity of Subject: headers. If you're composing a paragraph it ought to be in the message body, not the subject. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Majordomo drops multi-line Subject:
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Even though multi-line Subject: is theoretically legal according to the > >> RFCs, it's certainly an awful idea; how many MUAs do you know that > >> provide more than one line to display the subject in a normal view? > >> So I don't really care if Majordomo truncates the subject --- I wouldn't > >> see the rest of it anyway. > > > Huh, but the MUA auto-unfolds it for view. Both mutt and Elm do that > > fine -- the folding and unfolding. I would think exmh is pretty > > thoroughly broken if it didn't. > > Well, if I actually choose to read the message, sure I'll see all of it. > The point here is that you've got one line (and only about 50 characters > at that) to get my attention, and so I'm perfectly fine with list > software that, erm, strongly encourages brevity of Subject: headers. > If you're composing a paragraph it ought to be in the message body, > not the subject. Have a look at how mutt displays the message index: 69 L Aug 23 Zdenek Kotala ( 37) Re: [PATCHES] Allow commenting of variables in postgresql.conf to - 70 L Aug 23 Bruce Momjian ( 52) ->Re: [PATCHES] Allow commenting of variables in Note that the rest of the second subject line could still use the same space as the line above it. I mostly don't use 80-line terminals to read mail anymore because there's so much stuff that's too wide. Subjects have already less space available because of those [FOOBAR] stuff that's prepended to it. (I noticed a couple of days ago that you strip those. Maybe I should do that too.) In any case I don't see any reason to let the broken software continue to be broken. Surely there must be an updated version which corrects this bug? A patch at least? I mean, I can't be the only one complaining about it. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Majordomo drops multi-line Subject:
Even though multi-line Subject: is theoretically legal according to the RFCs, it's certainly an awful idea; how many MUAs do you know that provide more than one line to display the subject in a normal view? So I don't really care if Majordomo truncates the subject --- I wouldn't see the rest of it anyway. Well my MUA actually reads the new line and makes it a single line. Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] How to become Stats in postgresql?
Hello People, i need a command to become a cpu stats, cpu load, max query per sec, max transaktion etc. in the Postgresql? David Collrep
[GENERAL] help with postgresql and stats
Hello, which command to become stats, cpu load, max query per sec and transaction over postgresql? thank you david collrep
[GENERAL] Row type select syntax - inconsistency or misunderstanding?
(PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.2
20050808 (prerelease) (Ubuntu 4.0.1-4ubuntu9))
In trying to retrieve a row as a composite rowtype from a table, I'm running
into what appears to be an inconsistent result based on whether I select *,
table.*, or the list of columns in the table:
test=# select row(*) from thing;
ERROR: syntax error at or near "*" at character 12
LINE 1: select row(*) from thing
^
test=# select row(thing.*) from thing;
row
--
("(1,aardvark)")
(1 row)
test=# select row(id, name) from thing;
row
--
(1,aardvark)
(1 row)
test=# select row((id, name)) from thing;
row
--
("(1,aardvark)")
(1 row)
1. It seems that this may be an inconsistency in how Postgres is returning
rowtypes. row(thing.*) is behaving like row((col1, col2)), and row(*) just
breaks. I understand why the double-parens in the last select do what
they do; the others puzzle me.
2. As a workaround, perhaps I could "unrow" the double-rowed version returned
by row(thing.*) or row ((col1,col2)). However, I did not see a function listed
in the manual for this purpose. Is there such a function?
3. My end goal is to make audit tables by having the audit tables store
a column of composite type (namely, the row type of the table being
audited).
Here's an example schema where I want to audit the "thing" table by dropping a
thing rowtype directly into the thing_audit table:
CREATE TABLE thing (
id integer NOT NULL,
name text
);
CREATE TABLE thing_audit (
audit_id integer NOT NULL,
thing_row thing
);
INSERT INTO thing (id, name) VALUES (1, 'aardvark');
I'd like to be able to say something like:
INSERT INTO thing_audit (id, thing_row)
SELECT 101, ROW(thing.*) FROM thing WHERE id=1;
However, the behavior of rows doesn't seem to play nice. It seems like
I could do this with an exhaustive listing of columns in my audited
table, but that seems kludgey.
Any thoughts on either the "unrow" function workaround or my end goal?
Best,
Randall
--
Randall Lucas Tercent, Inc. DF93EAD1
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] CASCADING could not open relation with OID
I’ve seen that some other people have had ERROR could not open relation with OID ### The suggested cause was somebody trying to drop a table in the middle of VACUUM. In my case, the error seems to be spreading. Initially it affected only one table in a staging area that would get TRUNCATEd and populated every night. Now I’m starting to see it in more tables with similar functionality. These get populated by scripts that run at night. Some of these are crashing. You can see a log below. Has somebody experienced this before? I’m getting worried, could this be due to a faulty disk? In the short term, I think I’m just going to recreate the tables from a backup of the schema. Any suggestions? Thanks Jaime 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. CONTEXT: writing block 5529 of relation 1663/16390/686426795 *** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. *** ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Row type select syntax - inconsistency or misunderstanding?
Randall Lucas <[EMAIL PROTECTED]> writes: > In trying to retrieve a row as a composite rowtype from a table, I'm running > into what appears to be an inconsistent result based on whether I select *, > table.*, or the list of columns in the table: FWIW, we've changed the behavior of ROW(foo.*) for 8.2 --- it now behaves as if you'd written out all the columns of foo explicitly. I don't have a solution for you in 8.1 short of writing 'em all out :-( > I'd like to be able to say something like: > INSERT INTO thing_audit (id, thing_row) > SELECT 101, ROW(thing.*) FROM thing WHERE id=1; In CVS HEAD this seems to work except you have to explicitly cast the ROW constructor: regression=# select * from int8_tbl; q1|q2 --+--- 123 | 456 123 | 4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 (5 rows) regression=# create table a_int8_tbl(id int, data int8_tbl); CREATE TABLE regression=# insert into a_int8_tbl select 101,row(int8_tbl.*) from int8_tbl; ERROR: cannot cast type record to int8_tbl regression=# insert into a_int8_tbl select 101,row(int8_tbl.*)::int8_tbl from int8_tbl; INSERT 0 5 regression=# select * from a_int8_tbl; id | data -+-- 101 | (123,456) 101 | (123,4567890123456789) 101 | (4567890123456789,123) 101 | (4567890123456789,4567890123456789) 101 | (4567890123456789,-4567890123456789) (5 rows) regression=# I don't remember at the moment why we insist on the explicit cast. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Row type select syntax - inconsistency or misunderstanding?
On Wed, Aug 23, 2006 at 02:31:35PM -0400, Tom Lane wrote:
> Randall Lucas <[EMAIL PROTECTED]> writes:
> > In trying to retrieve a row as a composite rowtype from a table, I'm running
> > into what appears to be an inconsistent result based on whether I select *,
> > table.*, or the list of columns in the table:
>
> FWIW, we've changed the behavior of ROW(foo.*) for 8.2 --- it now
> behaves as if you'd written out all the columns of foo explicitly.
> I don't have a solution for you in 8.1 short of writing 'em all out :-(
If one is willing to construct an expression (for example, for use in
an EXECUTE within a plpgsql function), the following works in 8.1.3:
create or replace function audit_table_thing(int) returns int as $$
declare
in_id alias for $1;
record_text text;
begin
select into record_text quote_literal(textin(record_out(thing.*))) from
thing where id=in_id;
raise notice '%', record_text;
execute 'insert into thing_audit (audit_id, thing_row) values (' || in_id
|| ', cast(' || record_text || ' as thing) )';
return 1;
end
$$ language 'plpgsql';
Is it safe to say that the row functions are bleeding edge?
Randall
--
Randall Lucas Tercent, Inc. DF93EAD1
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] pl/R problem
Alvaro Herrera wrote: Don Isgitt wrote: Michael Fuhr wrote: On Wed, Aug 23, 2006 at 08:38:01AM -0500, Don Isgitt wrote: Thanks for the reply and the research. The relevant file is [EMAIL PROTECTED] R-2.3.0]$ ls -l $R_HOME/etc/Renviron -rw-rw-r--1 djisgitt djisgitt 1151 Jun 1 11:42 /home/djisgitt/R-2.3.0/etc/Renviron so I don't see that as the problem. Have you checked the permissions on all of the directories in the file's path? Have you verified that PostgreSQL is using the same $R_HOME? You can check the environment with plr_environ(): Yes. At least world read all the way. The best way to make sure this is the case I've found is su - postgres stat /home/djisgitt/R-2.3.0/etc/Renviron (or ls, whatever) I've seen people swearing they have world access all the way and then noticing they are missing a little bit somewhere. Quite so, Alvaro & Michael. Yes, world read as I said, but missing execute at one level. Sorry for my carelessness. It works as expected now. Thank you, Don ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] How to detect Postgres deadlocks?
Hi,We are using Postgres 7.4.5, and I'm trying to find a way to detect and gather deadlock information.1) Which sql queries should I use to detect deadlocks while they are happening? I see the deadlock info on the log file, but I'd like to query the database to see them as they happen... 2) Which fields on which pg catalogs indicate a deadlock condition?3) How can I get more info about the processes involved on deadlocks?4) How can I get the sql statements associated with the processes involved on deadlocks? On the logs I see the procpids of the processes involved on past deadlocks, but I 'd like to know how to get those procpids, and how to get the "current_query" associated with them...Thank you in advance, Andre Philippi
Re: [GENERAL] pl/R problem
Don Isgitt wrote: Quite so, Alvaro & Michael. Yes, world read as I said, but missing execute at one level. Sorry for my carelessness. It works as expected now. Ah, good to know. I'll add myself a todo to eliminate the crash in this scenario. I will include a fix in the next release, which will probably be sometime after postgres 8.2 beta starts. Joe ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to detect Postgres deadlocks?
On Wed, 2006-08-23 at 14:45, andre wrote: > Hi, > > We are using Postgres 7.4.5, and I'm trying to find a way to detect > and gather deadlock information. > > 1) Which sql queries should I use to detect deadlocks while they are > happening? I see the deadlock info on the log file, but I'd like to > query the database to see them as they happen... > > 2) Which fields on which pg catalogs indicate a deadlock condition? > > 3) How can I get more info about the processes involved on deadlocks? > > 4) How can I get the sql statements associated with the processes > involved on deadlocks? > > On the logs I see the procpids of the processes involved on past > deadlocks, but I 'd like to know how to get those procpids, and how to > get the "current_query" associated with them... I'm not sure what you're really looking for. When PostgreSQL detects a deadlock, it aborts one of the queries to stop the deadlock right away. test=> update l set b=22 where i=2; ERROR: deadlock detected DETAIL: Process 25854 waits for ShareLock on transaction 11654043; blocked by process 24918. Process 24918 waits for ShareLock on transaction 11654047; blocked by process 25854. test=> That deadlock detection took about 1 second and rolled back my transaction immediately. I'm not sure you can detect them in real time any better than pgsql can. Or are you experiencing some kind of deadly embrace problem??? Tell us what problem you're having and maybe we can come up with some better advice. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] invalid byte sequence ?
Hi, I've got pg 8.1.4 from the binary Windows installer. Windows 2000 / German Now I entered "\d" into psql on the text-console and got this: db_test=# \d ERROR: invalid byte sequence for encoding "UTF8": 0xfc6d6572220a What's up ? db_test was created UTF8 encoded ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] invalid byte sequence ?
Andreas wrote: > Hi, > > I've got pg 8.1.4 from the binary Windows installer. > Windows 2000 / German > Now I entered "\d" into psql on the text-console and got this: > > db_test=# \d > ERROR: invalid byte sequence for encoding "UTF8": 0xfc6d6572220a > > What's up ? > db_test was created UTF8 encoded What does your client_encoding show? It should be UTF8 too. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] CASCADING could not open relation with OID
"Silvela, Jaime \(Exchange\)" <[EMAIL PROTECTED]> writes: > WARNING: terminating connection because of crash of another server > process This is not an interesting message: the interesting message is the previous one about exactly what happened to the other process. Look earlier in the server log. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] invalid byte sequence ?
Bruce Momjian schrieb: Andreas wrote: I've got pg 8.1.4 from the binary Windows installer. Windows 2000 / German Now I entered "\d" into psql on the text-console and got this: db_test=# \d ERROR: invalid byte sequence for encoding "UTF8": 0xfc6d6572220a What's up ? db_test was created UTF8 encoded What does your client_encoding show? It should be UTF8 too. it is. db_test=# \d ERROR: invalid byte sequence for encoding "UTF8": 0xfc6d6572220a db_test=# show client_encoding; client_encoding - UTF8 (1 Zeile) psql complains about the code page, too, now. (850 vs. 1252) I'm sure I checked it the other day with a cmd that used 1252 and still got the error for the \d command. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] CASCADING could not open relation with OID
Actually the server logging was disabled, which I am now enabling. But scripts have been complaining about not finding this or that relation with OID x. I've located each of the tables and am trying to recreate them. If I try to read from one, I get ERROR: could not open relation with OID 16896 If I try to redefine it, I get ERROR: relation "bb_master" already exists If I try to DROP it, ERROR: cache lookup failed for relation 16896 How can I get around this? What's happening? Tomorrow I should have better logs on the initially reported problem. Many thanks Jaime -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 5:20 PM To: Silvela, Jaime (Exchange) Cc: pgsql-general Subject: Re: [GENERAL] CASCADING could not open relation with OID "Silvela, Jaime \(Exchange\)" <[EMAIL PROTECTED]> writes: > WARNING: terminating connection because of crash of another server > process This is not an interesting message: the interesting message is the previous one about exactly what happened to the other process. Look earlier in the server log. regards, tom lane *** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. *** ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Inserting Data
Michael Perhaps we can look at the following as a simple example of what is happening- - create or replace function loop_association() returns trigger as $$ begin Insert Into p_id.loops (monitor) select new.devices_id from p_id.devices ; return null ; end ; $$ language plpgsql ; create trigger loop after insert on p_id.devices for each row execute procedure loop_association(); -- This trigger and procedure gives a single row on the first insert on an otherwise blank table. However it produces two identical rows of the second device_id on the second insert and three identical rows of the third device_id on the third insert. (This is the only trigger on the table) If I read your message correctly the trigger is firing on each row of the originating table and each time it fires it produces a row on the secondary table for the current NEW.device_id. How can I correct this action? Bob - Original Message - From: "Michael Fuhr" <[EMAIL PROTECTED]> To: "Bob Pawley" <[EMAIL PROTECTED]> Cc: "Postgresql" Sent: Tuesday, August 22, 2006 1:58 PM Subject: Re: [GENERAL] Inserting Data On Tue, Aug 22, 2006 at 09:02:52AM -0700, Bob Pawley wrote: I set aside the procedure you sent to me as it resulted in multiple rows of the same information. (In fact one variation produced 100 rows for each of the 9 "new" fields creating a 900 row table. If it was doing that then it would be a good idea to understand why. If the INSERT ... SELECT matched several rows then several rows would be inserted, and if the trigger fired for several rows then several INSERTs would be run. In contrast here is the trigger for the tables with which I am now working. As best as I can determine the two triggers are the same format. Note the trigger is an 'after update' as opposed to 'after insert'. [...] This trigger results in three rows of each "new" field. What's the exact update command and how many rows in p_id.devices does it affect? If the update modifies three rows then the trigger will fire three times (because it's defined FOR EACH ROW), resulting in three inserts. That could explain the insert-vs-update difference because an ordinary insert affects only one row. If you add a RAISE statement to the trigger function then you'll see when and how many times it's being called. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] CASCADING could not open relation with OID
"Silvela, Jaime \(Exchange\)" <[EMAIL PROTECTED]> writes: > If I try to read from one, I get ERROR: could not open relation with > OID 16896 > If I try to redefine it, I get ERROR: relation "bb_master" already > exists > If I try to DROP it, ERROR: cache lookup failed for relation 16896 What do you get from select oid, * from pg_class where relname = 'bb_master'; select oid, * from pg_class where oid = 16896; If either one fails to get a hit, try it again after doing set enable_indexscan = off; > How can I get around this? What's happening? Hard to tell. It sounds a bit like pg_class catalog damage, but the above experiment will tell us more. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Inserting Data
Bob Pawley <[EMAIL PROTECTED]> writes: > Perhaps we can look at the following as a simple example of what is > happening- > - > create or replace function loop_association() returns trigger as $$ > begin > Insert Into p_id.loops (monitor) > select new.devices_id > from p_id.devices ; > return null ; > end ; > $$ language plpgsql ; > create trigger loop after insert on p_id.devices > for each row execute procedure loop_association(); > -- > This trigger and procedure gives a single row on the first insert on an > otherwise blank table. However it produces two identical rows of the second > device_id on the second insert and three identical rows of the third > device_id on the third insert. (This is the only trigger on the table) Well, of course, because that's an unqualified "select", so each call will copy *all* of p_id.devices into p_id.loops. Methinks what you really want is to insert the NEW row, not the whole table. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Inserting Data
I thought the NEW qualified the select. If not, how is select qualified?? Thanks Bob - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Bob Pawley" <[EMAIL PROTECTED]> Cc: "Michael Fuhr" <[EMAIL PROTECTED]>; "Postgresql" Sent: Wednesday, August 23, 2006 3:18 PM Subject: Re: [GENERAL] Inserting Data Bob Pawley <[EMAIL PROTECTED]> writes: Perhaps we can look at the following as a simple example of what is happening- - create or replace function loop_association() returns trigger as $$ begin Insert Into p_id.loops (monitor) select new.devices_id from p_id.devices ; return null ; end ; $$ language plpgsql ; create trigger loop after insert on p_id.devices for each row execute procedure loop_association(); -- This trigger and procedure gives a single row on the first insert on an otherwise blank table. However it produces two identical rows of the second device_id on the second insert and three identical rows of the third device_id on the third insert. (This is the only trigger on the table) Well, of course, because that's an unqualified "select", so each call will copy *all* of p_id.devices into p_id.loops. Methinks what you really want is to insert the NEW row, not the whole table. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Inserting Data
Bob Pawley <[EMAIL PROTECTED]> writes: > I thought the NEW qualified the select. Not at all; that would rather cripple the ability to write interesting triggers. I think what you are really wanting to do here is just insert into p_id.loops (monitor) values (new.devices_id); regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] CASCADING could not open relation with OID
I get values frin the first statement but not from the second. After setting indexscan to off, still the same thing. Should this setting be off in general? -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 6:15 PM To: Silvela, Jaime (Exchange) Cc: pgsql-general Subject: Re: [GENERAL] CASCADING could not open relation with OID "Silvela, Jaime \(Exchange\)" <[EMAIL PROTECTED]> writes: > If I try to read from one, I get ERROR: could not open relation with > OID 16896 > If I try to redefine it, I get ERROR: relation "bb_master" already > exists > If I try to DROP it, ERROR: cache lookup failed for relation 16896 What do you get from select oid, * from pg_class where relname = 'bb_master'; select oid, * from pg_class where oid = 16896; If either one fails to get a hit, try it again after doing set enable_indexscan = off; > How can I get around this? What's happening? Hard to tell. It sounds a bit like pg_class catalog damage, but the above experiment will tell us more. regards, tom lane *** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. *** ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] invalid byte sequence ?
Andreas <[EMAIL PROTECTED]> writes: > I've got pg 8.1.4 from the binary Windows installer. > Windows 2000 / German > Now I entered "\d" into psql on the text-console and got this: > > db_test=# \d > ERROR: invalid byte sequence for encoding "UTF8": 0xfc6d6572220a I can replicate this by using a UTF8 database and running the client in a non-UTF8 locale. For example $ LANG=de_DE.iso88591 psql postgres Dies ist psql 8.2devel, das interaktive PostgreSQL-Terminal. Geben Sie ein: \copyright für Urheberrechtsinformationen \h für Hilfe über SQL-Anweisungen \? für Hilfe über interne Anweisungen \g oder Semikolon, um eine Anfrage auszuführen \q um zu beenden postgres=# \l ERROR: invalid byte sequence for encoding "UTF8": 0xfc6d657c TIP: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". postgres=# \d ERROR: invalid byte sequence for encoding "UTF8": 0xfc6d6572220a TIP: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". postgres=# \encoding UTF8 postgres=# The problem here is that psql is using gettext() to convert column headings for its display to German, and gettext() sees its locale as specifying ISO8859-1, so that's the encoding it produces. When that data is sent over to the server --- which thinks that the client is using UTF8 encoding, because it hasn't been told any different --- the server quite naturally barfs. We've known about this and related issues with gettext for some time, but a bulletproof solution isn't clear. For the moment all you can do is be real careful about making your locale settings match up. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] invalid byte sequence ?
Is this a TODO? --- Tom Lane wrote: > Andreas <[EMAIL PROTECTED]> writes: > > I've got pg 8.1.4 from the binary Windows installer. > > Windows 2000 / German > > Now I entered "\d" into psql on the text-console and got this: > > > > db_test=# \d > > ERROR: invalid byte sequence for encoding "UTF8": 0xfc6d6572220a > > I can replicate this by using a UTF8 database and running the client > in a non-UTF8 locale. For example > > $ LANG=de_DE.iso88591 psql postgres > Dies ist psql 8.2devel, das interaktive PostgreSQL-Terminal. > > Geben Sie ein: \copyright f?r Urheberrechtsinformationen > \h f?r Hilfe ?ber SQL-Anweisungen > \? f?r Hilfe ?ber interne Anweisungen > \g oder Semikolon, um eine Anfrage auszuf?hren > \q um zu beenden > > postgres=# \l > ERROR: invalid byte sequence for encoding "UTF8": 0xfc6d657c > TIP: This error can also happen if the byte sequence does not match the > encoding expected by the server, which is controlled by "client_encoding". > postgres=# \d > ERROR: invalid byte sequence for encoding "UTF8": 0xfc6d6572220a > TIP: This error can also happen if the byte sequence does not match the > encoding expected by the server, which is controlled by "client_encoding". > postgres=# \encoding > UTF8 > postgres=# > > The problem here is that psql is using gettext() to convert column > headings for its display to German, and gettext() sees its locale > as specifying ISO8859-1, so that's the encoding it produces. When > that data is sent over to the server --- which thinks that the > client is using UTF8 encoding, because it hasn't been told any > different --- the server quite naturally barfs. > > We've known about this and related issues with gettext for some time, > but a bulletproof solution isn't clear. For the moment all you can > do is be real careful about making your locale settings match up. > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] invalid byte sequence ?
I wrote: > We've known about this and related issues with gettext for some time, > but a bulletproof solution isn't clear. For the moment all you can > do is be real careful about making your locale settings match up. I forgot to mention that it works fine if the server is told the client encoding actually being used: postgres=# \encoding iso8859-1 postgres=# \l Liste der Datenbanken Name| Eigentümer | Kodierung ++--- postgres | tgl| UTF8 regression | tgl| SQL_ASCII template0 | tgl| UTF8 template1 | tgl| UTF8 (4 Zeilen) postgres=# \d Keine Relationen gefunden postgres=# A possible solution therefore is to have psql or libpq drive the client_encoding off the client's locale environment instead of letting it default to equal the server_encoding. But I'm not sure what downsides that would have, and in any case it's not entirely clear that we can always derive the correct Postgres encoding name from the system's locale info. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Inserting Data
Thanks Tom But my problem with this solution comes whan I try to qualify with a 'where' clause. For instance - create or replace function loop_association() returns trigger as $$ begin insert into p_id.loops (monitor) values (new.devices_id) where new.device_number = library.devices.device_number and library.devices.type_ = 'mon' ; return null ; end ; $$ language plpgsql ; create trigger loop after insert on p_id.devices for each row execute procedure loop_association(); Gives me an error. What am I doing wrong? Bob - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Bob Pawley" <[EMAIL PROTECTED]> Cc: "Michael Fuhr" <[EMAIL PROTECTED]>; "Postgresql" Sent: Wednesday, August 23, 2006 3:31 PM Subject: Re: [GENERAL] Inserting Data Bob Pawley <[EMAIL PROTECTED]> writes: I thought the NEW qualified the select. Not at all; that would rather cripple the ability to write interesting triggers. I think what you are really wanting to do here is just insert into p_id.loops (monitor) values (new.devices_id); regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] CASCADING could not open relation with OID
"Silvela, Jaime \(Exchange\)" <[EMAIL PROTECTED]> writes: > I get values frin the first statement but not from the second. What values exactly ... particularly the OID? > After setting indexscan to off, still the same thing. > Should this setting be off in general? Certainly not! That was just an experiment to see if your problem was corruption of the indexes on pg_class. Seems like not. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [SLE] Install source
On Sunday 20 August 2006 03:46, Anders Johansson wrote: > On Sunday 20 August 2006 15:29, Chris Herrnberger wrote: > > Hay my main bitch with a smile is that since 5.2 I could point yast to > > any directory on my machine, remote or local and include the directory as > > a yast source, however more typically used on my own machine. Now I have > > to create a yast repository, and go to all that work, just to include an > > existing directory on my laptop say for example the latest KDE just to > > ensure that all dependencies are addressed. Used to be as simple as point > > an click (and since 5.2) Now its a half hour exercise. > > YaST1 could do that, and for a long time YaST2 couldn't. But in the later > releases, you have the possibility to add a "local directory" as an > installation source in YaST, and it will take it even if it isn't in > repository format (IOW it's just a directory of rpms) > > From the command line, it's "rug mount /path/to/dir " and it gets > added as an installation source Is it possible to use Yast in 10.1 in the same way as 10.0? Thanks, Jerome ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] CASCADING could not open relation with OID
I get Oid: 16896 Relname: bb_master Relnamespace: 16392 Reltype: 16897 ... Reltablespace: 0 Relpages: 0 Reltuples: 0 ... Relkind: r ... Thanks Jaime -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 6:54 PM To: Silvela, Jaime (Exchange) Cc: pgsql-general Subject: Re: [GENERAL] CASCADING could not open relation with OID "Silvela, Jaime \(Exchange\)" <[EMAIL PROTECTED]> writes: > I get values frin the first statement but not from the second. What values exactly ... particularly the OID? > After setting indexscan to off, still the same thing. > Should this setting be off in general? Certainly not! That was just an experiment to see if your problem was corruption of the indexes on pg_class. Seems like not. regards, tom lane *** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. *** ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Inserting Data
Bob Pawley <[EMAIL PROTECTED]> writes: > insert into p_id.loops (monitor) values (new.devices_id) > where new.device_number = library.devices.device_number > and library.devices.type_ = 'mon' ; Huh? How did library.devices get into this? Are you trying to join to it, and if so why, seeing that the value you want to insert into p_id.loops is independent of that table? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] XPath and XML support
I'm doing a performance and scalability test project for a PostgreSQL user who is working with geospatial data. The data is in GML form. For instance: http://www.opengis.net/gml";> 7.0,9.0 I installed PostGIS and it supports Point and coordinates very well. I an not sure what I should do with the XML content using PostgreSQL? For example, 1) Tom Dyson at http://www.throwingbeans.org/ postgresql_and_xml_updated.html says "PostgreSQL 8 ships with xpath_table to evaluate a set of XPath queries and returns the results as a virtual table." These look like good XPath functions. Are they actually in PSQL 8.1.4? I did not find them in the Windows installed version. If not, are they recommended? 2) What is the relationship of the XPath functions to the PostGIS functions? For example, will I be able to use an index to a PostGIS field? 3) If the XPath operators are not allowed, should I shred the GML into two tables (point and detail)? Any help is much appreciated. Thanks. -Frank --- Frank Cohen, Raining Data, http://www.RainingData.com, phone: 408 236 7604 http://www.xquerynow.com for free XML, XQuery and native XML database tips, techniques and solutions. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] CASCADING could not open relation with OID
"Silvela, Jaime \(Exchange\)" <[EMAIL PROTECTED]> writes: > I get > Oid: 16896 > Relname: bb_master Hmm ... but you're *sure* "where oid = 16896" can't find this row, even with enable_indexscan = off? That doesn't make a lot of sense. To cut to the chase, though: try "REINDEX pg_class" and see if it helps. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] CASCADING could not open relation with OID
It hadn't occurred to me to reindex the pg_class!! Beginner... After reindexing, both query lines were successful, and I was able to access my missing tables!! THANKS!! What could be the possible cause for this? Thanks again, Jaime -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 7:10 PM To: Silvela, Jaime (Exchange) Cc: pgsql-general Subject: Re: [GENERAL] CASCADING could not open relation with OID "Silvela, Jaime \(Exchange\)" <[EMAIL PROTECTED]> writes: > I get > Oid: 16896 > Relname: bb_master Hmm ... but you're *sure* "where oid = 16896" can't find this row, even with enable_indexscan = off? That doesn't make a lot of sense. To cut to the chase, though: try "REINDEX pg_class" and see if it helps. regards, tom lane *** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. *** ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Inserting Data
What I have is one table which stores device_id numbers that are referenced on the second table "library.devices". I need to insert device_ids from the first table that satisfy the conditions of the argument found on the library table. Hence the 'where' clause. So far all I can get are errors when I attempt this procedure. Hence - my problem. Bob - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Bob Pawley" <[EMAIL PROTECTED]> Cc: "Michael Fuhr" <[EMAIL PROTECTED]>; "Postgresql" Sent: Wednesday, August 23, 2006 4:05 PM Subject: Re: [GENERAL] Inserting Data Bob Pawley <[EMAIL PROTECTED]> writes: insert into p_id.loops (monitor) values (new.devices_id) where new.device_number = library.devices.device_number and library.devices.type_ = 'mon' ; Huh? How did library.devices get into this? Are you trying to join to it, and if so why, seeing that the value you want to insert into p_id.loops is independent of that table? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] CASCADING could not open relation with OID
"Silvela, Jaime \(Exchange\)" <[EMAIL PROTECTED]> writes: > What could be the possible cause for this? Hard to say ... have you had any hardware flakiness lately? Are you running an up-to-date PG release? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Inserting Data
Bob Pawley <[EMAIL PROTECTED]> writes: > What I have is one table which stores device_id numbers that are referenced > on the second table "library.devices". > I need to insert device_ids from the first table that satisfy the conditions > of the argument found on the library table. Hence the 'where' clause. This isn't real clear to me, but perhaps you are looking for something like IF EXISTS(select 1 from library.devices where ...) THEN INSERT INTO ... values(new.device_id); END IF; regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How do i store arbitrary questions and answers in SQL?
lifeisgood wrote: The problem : to store, in SQL/RDBMS, an arbitrary set of questions and their answers, where the questions are text (obviously) but the answers can be dates, text, integers, money etc. think of it as a big questionnaire where at design time we have no idea what the questions will be. My usual solution to this problem is to store everything in varchar and flag the type, converting as I extract data. It is not a desirable solution. i.e. CREATE TABLE Qu (ID INT, Question VARCHAR(64)) CREATE TABLE Answer(ID INT, questionID INT, answer_text VARCHAR(255), datatype INT) Use text, not varchar - varchar is just a waste of time and space. This approach isn't all that bad, since if you're using libpq or similar to read and write the values (without using a binary cursor), you're using a text representation anyway. Just use the same text representation of your data that the db interface is going to use. Are there any other solutions out there? The other solution is to partition your table, make some number of tables, one for each data type, with the value column using the correct native type. Your code that reads and writes values then needs to be smart enough to use the correct table depending on the data type. We've used both approaches, and they both work fine for us. The text for everything approach has the advantage of simplicity of interface code, whereas partitioning on data type gives you better query planning and better performance when you have a lot (ie millions of rows plus) of data. I can think of several ideas but they all fall short in some way 0. (current one) I have to cast any search term to string first but after that the SQL performs as one expects. No, you don't have to "cast" things to text - quite the reverse; if you are querying on the contents of your value (answer) column and your criterion depends on the correct type (eg find questions with integer answers greater than 42) then you have to cast the text to integer in the query. 1. store the serialised object in binary form. (how does one search this? Totally dependant on choice of middleware language) I'd avoid this one - for the reason you've mentioned, among others. Not sure what the middleware language has to do with it, though - if your choice of middleware makes things harder then it's the wrong choice. If middleware doesn't make things easier, then what use is it? 2. Store different types in diff columns table answer (questionID, ans_text VARCHAR, ans_money MONEY, ans_int INT But this makes searching through SQL even harder than casting, as in each query i must what answer to expect. Definitely avoid this one. Lots of wasted space, as well as extra software complexity, with little payoff. 3. Different answer tables each with answer types - same problem as 2 but even harder. This is the other option I mentioned above. It's not hard at all. 4. I suspect pl/python might be useful, but I cannot see how at the moment... I can't see it either :). Decide what you want to do first, _then_ work out how to implement it. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] XPath and XML support
On Wed, 2006-08-23 at 16:04 -0700, Frank Cohen wrote: > I'm doing a performance and scalability test project for a PostgreSQL > user who is working with geospatial data. The data is in GML form. > For instance: > > > > > >http://www.opengis.net/gml";> > 7.0,9.0 > > > > > > I installed PostGIS and it supports Point and coordinates very well. > > I an not sure what I should do with the XML content using PostgreSQL? > For example, > > 1) Tom Dyson at http://www.throwingbeans.org/ > postgresql_and_xml_updated.html says "PostgreSQL 8 ships with > xpath_table to evaluate a set of XPath queries and returns the > results as a virtual table." > > These look like good XPath functions. Are they actually in PSQL > 8.1.4? I did not find them in the Windows installed version. If not, > are they recommended? Look at contrib/xml2. The function xpath_table is in that module. I'm not familiar enough with windows to tell you how to install it, but hopefully this gives you a good starting point. Regards, Jeff Davis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Inserting Data
I'm not arguing (I'm attempting to learn) - but this seems to be counter intuitive when writing a procedure. I know that it exists because, through the interface, I have selected it from the same library table. Could you explain why Postgresql simply doesn't accept the simple 'where' statement that was in my earlier e-mail. Bob - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Bob Pawley" <[EMAIL PROTECTED]> Cc: "Michael Fuhr" <[EMAIL PROTECTED]>; "Postgresql" Sent: Wednesday, August 23, 2006 4:44 PM Subject: Re: [GENERAL] Inserting Data Bob Pawley <[EMAIL PROTECTED]> writes: What I have is one table which stores device_id numbers that are referenced on the second table "library.devices". I need to insert device_ids from the first table that satisfy the conditions of the argument found on the library table. Hence the 'where' clause. This isn't real clear to me, but perhaps you are looking for something like IF EXISTS(select 1 from library.devices where ...) THEN INSERT INTO ... values(new.device_id); END IF; regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Inserting Data
Let me explain. I have a table called p_id.devices which accumulates the devices_id for a multitude of differing devices used in P&ID development.(Process Engineering) I also have a table called library.devices which is ( or soon will be ) a detailed explanation of all of the particular devices available. I accumulate the device_ids of the devices used during the P&ID development. What I need to do now is distribute the various devices to their own tables (Loops as well as others) based on the information found in the library.devices table. I'm trying to make best use of the relationship features of a relational data base. However, I am frustrated by what appears to be a restrictive use of simple logic. I am sure there is a reason for developing general SQL and PostgreSQL in the manner in which it has developed. I am just trying to parse the details behind the structure as best I can. Bob - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Bob Pawley" <[EMAIL PROTECTED]> Cc: "Michael Fuhr" <[EMAIL PROTECTED]>; "Postgresql" Sent: Wednesday, August 23, 2006 4:44 PM Subject: Re: [GENERAL] Inserting Data Bob Pawley <[EMAIL PROTECTED]> writes: What I have is one table which stores device_id numbers that are referenced on the second table "library.devices". I need to insert device_ids from the first table that satisfy the conditions of the argument found on the library table. Hence the 'where' clause. This isn't real clear to me, but perhaps you are looking for something like IF EXISTS(select 1 from library.devices where ...) THEN INSERT INTO ... values(new.device_id); END IF; regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Shared Objects (Dynamic loading)
Hi,I have a function in which i dynamicall load my shared object and the function definition is as follows:CREATE OR REPLACE FUNCTION sp_trigger_raw_email(int4, text) RETURNS bool AS'/usr/local/pgsql/jsbali/parser', 'parse_email' LANGUAGE 'c' VOLATILE STRICT;ALTER FUNCTION sp_trigger_raw_email(int4,text ) OWNER TO postgres;- function parse_email(int caseno, char *rawemail)populates a few global variables first and thencall another function parse_header().function parse_header() makes use of the global variables and then using ECPG stores values in a table in the database. My question is, when we try to make use of a specific function of a shared object dynamically loaded as show above, then would that function be able to access all global variables populated elsewhere in the program or all the global variables can't be accessed inside that function of the shared object. Also, in the above function definition,the signature of parse_email function isparse_email(int, char*) and i am passing (int4 , text) to int as seen in the function code pasted above.Is text in pgsql going to match with char* or i should use some other datatype? Thanks and regards,Jas
Re: [GENERAL] Shared Objects (Dynamic loading)
On Thu, Aug 24, 2006 at 01:03:43AM -0400, Jasbinder Bali wrote: > CREATE OR REPLACE FUNCTION sp_trigger_raw_email(int4, text) > RETURNS bool AS > '/usr/local/pgsql/jsbali/parser', 'parse_email' > LANGUAGE 'c' VOLATILE STRICT; > ALTER FUNCTION sp_trigger_raw_email(int4,text ) OWNER TO postgres; > > function parse_email(int caseno, char *rawemail) > populates a few global variables first and then > call another function parse_header(). > function parse_header() makes use of the global variables and then using > ECPG stores values in a table in the database. Is there a reason this server-side code is using ECPG instead of SPI? http://www.postgresql.org/docs/8.1/interactive/spi.html > My question is, when we try to make use of a specific function of a shared > object dynamically loaded as show above, then > would that function be able to access all global variables populated > elsewhere in the program or all the global variables can't be accessed > inside that function of the shared object. A function should be able to access any global symbol and any static symbol in the same object file. Are you having trouble doing so? > Also, in the above function definition, > the signature of parse_email function is > parse_email(int, char*) and i am passing (int4 , text) to int as seen in the > function code pasted above. > Is text in pgsql going to match with char* or i should use some other > datatype? See "C-Language Functions" in the documentation, in particular what it says about version 1 calling conventions. http://www.postgresql.org/docs/8.1/interactive/xfunc-c.html Is there a reason you're coding in C instead of a higher-level language like PL/Perl? If you're parsing email messages then coding in Perl, Python, Ruby, etc., would probably be easier than C. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [JDBC] org.postgresql.util.PSQLException: An I/O error occured while sending to the backend
Title: Re: [JDBC] org.postgresql.util.PSQLException: An I/O error occured while sending to the backend hi, dont such messages get logged to postgreslog. Also, is it possible to check wht firewall is dropping the connection between the client and the server. thanks, regards Surabhi From: Oliver Jowett [mailto:[EMAIL PROTECTED]Sent: Wed 8/23/2006 12:03 PMTo: surabhi.ahujaCc: pgsql-general@postgresql.org; pgsql-jdbc@postgresql.orgSubject: Re: [JDBC] org.postgresql.util.PSQLException: An I/O error occured while sending to the backend ***Your mail has been scanned by InterScan VirusWall.***-***surabhi.ahuja wrote:> org.postgresql.util.PSQLException: An I/O error occured while sending to> the backend.> Caused by: java.net.SocketException: Broken pipeThis is a network error that the driver can't do anything about. If youhave a stateful firewall between the client and the server, perhaps itis dropping the connection because it has been idle.-O
Re: [GENERAL] [JDBC] org.postgresql.util.PSQLException: An I/O error occured
surabhi.ahuja wrote: hi, dont such messages get logged to postgreslog. You might see something in the backend logs -- "unexpected client EOF" perhaps? Or just idle backend processes. It depends on exactly what is going wrong, and the timing of it. Also, is it possible to check wht firewall is dropping the connection between the client and the server. That is something you will have to diagnose yourself, it is specific to your network. -O ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Inserting Data
On Wed, Aug 23, 2006 at 05:34:27PM -0700, Bob Pawley wrote: > Could you explain why Postgresql simply doesn't accept the simple 'where' > statement that was in my earlier e-mail. Because INSERT doesn't take a WHERE clause. If you want to do the insert conditionally then use an IF statement as Tom suggested or use INSERT ... SELECT with a WHERE clause that would restrict the SELECT result to an empty set if the insert shouldn't happen. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
