Re: [GENERAL] optimizer ignoring primary key and doing sequence scan
Scott Marlowe ha scritto: On Mon, Jul 14, 2008 at 1:54 PM, Chris Hoy [EMAIL PROTECTED] wrote: Hi I have a number of tables in my database where the queries appear to ignoring the primary key and doing a seq scan instead, however other tables appear to be fine. I can see any difference between them. Is their any way of determination why the otimizer isn't picking up the primary key? Version 8.3.3 windows An example of a non working table is: select * from industries where industryid = 1; Seq Scan on industries (cost=0.00..1.02 rows=1 width=116) (actual time=0.011..0.013 rows=1 loops=1) According to this there's only one row in the table. why WOULD postgresql use an index when it can just scan the one row table in a split second. I agree with you that it can depend on the size of the table but where you can read that the table contains only one row? I try with my table (39910 rows, no index on column note) explain analyze select * from table where note='single example'; Seq Scan on table (cost=0.00..2458.88 rows=13 width=327) (actual time=10.901..481.896 rows=1 loops=1) On the postgres manual I can find Estimated number of rows output by this plan node (Again, only if executed to completion.) regarding the third parameter of the explain Where is my error? Edoardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backing up and deleting a database.
Hi, On Tue, Jul 15, 2008 at 2:52 PM, Andrew Maclean [EMAIL PROTECTED] wrote: We have a database that grows in size quite quickly. Of course we backup nightly and keep a weeks worth of data However we need to keep a few months data online, but the rest can be archived as it will be unlikley that it will be used again. As I see it we can: 1) Run a query to drop/delete old data, the downside here is that we lose it. 2) Stop the database (this is important because clients are writing to it), back it up, delete it and recreate the database. Has anyone done this? Do they have a script for htis? It sounds like table partitioning could be useful in your situation, depending on what your data looks like, and how you want to query it. Its worth your taking the time to read: http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html. If you're basically inserting a series of observations or something to a large table this could be useful - you can use it to increase the amount of data you can easily manage, and to automate something like a rolling 2-month window of online data. A script could be put together to periodically dump out the oldest partition, drop it, create a new partition, and maintain the associated triggers. Charles Duffy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inconsistency with stemming/stop words in Tsearch2
The list of stop-words is user defined, so you can just add 'whats' to
the list. We didn't insert it to the default list, since it's not
frequent as much as 'what'.
btw, you can use ts_debug function to see what's really happens:
=# select * from ts_debug('english','what''s');
alias | description | token | dictionaries | dictionary | lexemes
---+-+---++--+-
asciiword | Word, all ASCII | what | {english_stem} | english_stem | {}
blank | Space symbols | ' | {} | |
asciiword | Word, all ASCII | s | {english_stem} | english_stem | {}
(3 rows)
=# select * from ts_debug('english','whats');
alias | description | token | dictionaries | dictionary | lexemes
---+-+---++--+-
asciiword | Word, all ASCII | whats | {english_stem} | english_stem | {what}
(1 row)
On Mon, 14 Jul 2008, Yishai Lerner wrote:
Hi, having an issue with Tsearch2 and how stop words lexemes are sometimes
being utilized and sometimes not. I would expect the behavior for to_tsquery
for the three variations of what, what's and whats to be consistent
(using 'en_stem') and for all variations to be ignored since they all result
in a stop word of what. However, this is not the case as
to_tsquery(whats) returns the stop word what as a result. Even more
confusing is that if one were to look at the lexize results below, they are
inconsistent with the to_tsquery results below. This seems like a bug to me.
goodrec_2=# select lexize('en_stem', 'what''s');
lexize
{what}
goodrec_2=# select lexize('en_stem', 'whats');
lexize
{what}
goodrec_2=# select lexize('en_stem', 'what');
lexize
{}
goodrec_2=# select to_tsquery('what''s');
NOTICE: query contains only stopword(s) or doesn't contain lexeme(s),
ignored
to_tsquery
goodrec_2=# select to_tsquery('whats');
to_tsquery
'what'
goodrec_2=# select to_tsquery('what');
NOTICE: query contains only stopword(s) or doesn't contain lexeme(s),
ignored
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] C-procedure crashed in Postgres 8.3.3 when using 'text' variable (WinXP)
Hello!
I'd like to write C-function returning text for using in PG 8.3.3. (WinXP SP2)
For compilation I use MinGW-5.1.4 (gcc 3.4.5),MSYS-1.0.10.
The code looks like this (in reduced variant):
#include postgres.h
#include fmgr.h
#include executor/executor.h
#include utils/timestamp.h
#include utils/builtins.h
#include utils/formatting.h
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
#define PG_CSTR_GET_TEXT(cstrp) \
DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
PG_FUNCTION_INFO_V1(SomeFunction);
Datum
SomeFunction(PG_FUNCTION_ARGS)
{
PG_RETURN_TEXT_P(PG_CSTR_GET_TEXT(my_string));
}
Then I create stored procedure in PG:
CREATE OR REPLACE FUNCTION service.some_function () RETURNS text AS
'$libdir/some_dll', 'SomeFunction'
LANGUAGE C STRICT;
-
When I'm trying to use service.some_function PG fails.
I tried to use the example from documentation:
PG_FUNCTION_INFO_V1(concat_text);
Datum
concat_text(PG_FUNCTION_ARGS)
{
text *arg1 = PG_GETARG_TEXT_P(0);
text *arg2 = PG_GETARG_TEXT_P(1);
int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
text *new_text = (text *) palloc(new_text_size);
SET_VARSIZE(new_text, new_text_size);
memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1) - VARHDRSZ);
memcpy(VARDATA(new_text) + (VARSIZE(arg1) - VARHDRSZ),
VARDATA(arg2), VARSIZE(arg2) - VARHDRSZ);
PG_RETURN_TEXT_P(new_text);
}
CREATE FUNCTION concat_text(text, text) RETURNS text
AS '$libdir/some_dll', 'concat_text'
LANGUAGE C STRICT;
And I got the same result.
When I'm trying to return integer, f.e., evetything is fine.
Also all this examples worked fine with PostgreSQL 8.2.
I saw some similar problems - but I didn't find the solution (
http://archives.postgresql.org/pgsql-general/2008-05/msg00060.php
http://archives.postgresql.org/pgsql-general/2008-05/msg00097.php
Have somebody any ideas how to resolve this problem?
Thanks in advance,
Marina
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.3 Complie issue
Harvey, Allan AC wrote: I think we've seen this before: do you have utf8_and_euc_jis_2004.c in that directory? There seem to be some broken versions of tar out there that can't deal with extracting such a long file name from the distribution tarball. Thanks Tom, The c on the end was missing. Fixed that, how about this Given that you already know your tarball extraction mangled the output, perhaps you should delete the copy you extracted and start again using a trustworthy tree extracted using GNU tar or similar. It's reasonable to assume that other problems you encounter have the same root cause - your tar program's mangling of the tree. Try: tar xvzf postgresql-whatever.tar.gz -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multithreaded queue in PgSQL
Whole point is to have multiple services accessing same table and dividing the work, so locking with waiting for lock to be released is out of question. We are doing the same (newsletter) and there is no problem to lock the whole table for a short time with an advisory lock as the java id fetching worker is locking the table (that does not lock the table for reading or writing, it is only locking his java worker brothers that are using the same advisory lock), fetches, let's say, 50 id's of records marked as CREATED and changes their status to PROCESSING. Then several workers are getting the id's and fetch the needed data from the table independently and process and update them in parallel. We have 3 java machines getting id's for 10 parallel workers and everything works just fine. Getting the IDs is much much faster usually then real processing. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] optimizer ignoring primary key and doing sequence scan
On þri, 2008-07-15 at 08:19 +0200, Edoardo Panfili wrote: Scott Marlowe ha scritto: On Mon, Jul 14, 2008 at 1:54 PM, Chris Hoy [EMAIL PROTECTED] wrote: select * from industries where industryid = 1; Seq Scan on industries (cost=0.00..1.02 rows=1 width=116) (actual time=0.011..0.013 rows=1 loops=1) According to this there's only one row in the table. why WOULD postgresql use an index when it can just scan the one row table in a split second. I agree with you that it can depend on the size of the table but where you can read that the table contains only one row? it does not really say 1 row, but you can infer from the estimated cost, that the table is only 1 block (cost=0.00..1.02). that is the smallest read unit. using an index would cost 2 random reads. I try with my table (39910 rows, no index on column note) explain analyze select * from table where note='single example'; Seq Scan on table (cost=0.00..2458.88 rows=13 width=327) (actual time=10.901..481.896 rows=1 loops=1) surely this is not the same table gnari -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multithreaded queue in PgSQL
We are doing the same (newsletter) and there is no problem to lock the
whole table for a short time with an advisory lock as the java id
fetching worker is locking the table (that does not lock the table for
reading or writing, it is only locking his java worker brothers that
are using the same advisory lock), fetches, let's say, 50 id's of
records marked as CREATED and changes their status to PROCESSING. Then
several workers are getting the id's and fetch the needed data from
the table independently and process and update them in parallel. We
have 3 java machines getting id's for 10 parallel workers and
everything works just fine.
Getting the IDs is much much faster usually then real processing.
Weird this thread came back up today. I actually implemented a small
example this morning in Python using Klint Gore's suggestion and was
very happy with the results. I have attached 4 files for anyone
interested in looking at a really simple example. You'll need
Python+psycopg2 driver for this, but anyone should be able to follow
this easily:
db.py - Edit this file with your db settings.
client.py - This would be your worker process
insert.py - This is used to enter some jobs into the jobs table.
reset.py - This is used to reset each job back to 'REQUESTED' status.
This example assumes that you have a jobs table like below:
create table jobs(id serial primary key, status text not null)
First edit db.py with your own db settings.
Fire up as many copies of client.py as you'd like.
Now enter some jobs into jobs table. Running insert.py will enter 100
jobs for you.
Now watch as your clients process the jobs.
Once all of the jobs have finished processing, you can run reset.py to
mark all of the jobs back to 'REQUESTED' status so that the clients
start processing all over again.
I hope it is OK to attach examples! Just seems like this question
comes up often.
Jeff Peck
import psycopg2
host = 'localhost'
db = 'your_db'
user = 'your_username'
password = 'your_pw'
connect_string = dbname='%s' user='%s' host='%s' password='%s' % (
db, user, host, password
)
def connect_db():
try:
connection = psycopg2.connect(connect_string)
except Exception, e:
print Error connection to db!
raise
else:
print Successfull connection
return connection
import time
import psycopg2
import psycopg2.extensions
import db
def get_new_job_count(connection):
get_new_job_count(connection) - int
Returns the number of new jobs found in the jobs table. New jobs have
a status of 'REQUESTED'
cursor = connection.cursor()
cursor.execute(BEGIN TRANSACTION)
cursor.execute(
SELECT count(status)
FROM jobs
WHERE status = 'REQUESTED'
)
count = cursor.fetchall()[0][0]
cursor.execute(COMMIT)
return count
def get_new_jobs(connection):
get_new_jobs(connection) - [(id, status), ., (id_n, status_n)]
Returns new jobs found in jobs table. This will return a max of 10 jobs
at a time.
jobs = []
try:
cursor = connection.cursor()
cursor.execute(BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE)
cursor.execute(
UPDATE jobs
SETstatus = 'PROCESSING'
WHERE id IN(
SELECT id
FROM jobs
WHERE status = 'REQUESTED'
LIMIT 10
)
RETURNING *
)
jobs = cursor.fetchall()
cursor.execute(COMMIT)
except psycopg2.extensions.TransactionRollbackError, e:
# Another client had the job table locked. Just continue. We will have
# to retry the call to get_new_jobs again.
cursor.execute(COMMIT)
return jobs
def mark_complete(connection, jobs):
mark_complete(connection, jobs) - None
Sets the status flag for each passed in job to COMPLETE.
cursor = connection.cursor()
sql = UPDATE jobs SET status='COMPLETE' WHERE id=%(id)s
update_params = [{'id': job[0]} for job in jobs]
cursor.execute(BEGIN TRANSACTION)
cursor.executemany(sql, update_params)
cursor.execute(COMMIT TRANSACTION)
connection = db.connect_db()
my_job_ids = []
collisions = 0
while 1:
# First see if there are any new jobs entered into the queue
count = get_new_job_count(connection)
if not count:
if len(my_job_ids):
print Job queue empty. I processed these jobs:
my_job_ids.sort()
print my_job_ids
print I processed %d jobs % (len(my_job_ids))
print I had %d collisions % (collisions)
my_job_ids = []
collisions = 0
time.sleep(5)
continue
else:
print %d jobs remaining in queue % (count)
jobs = get_new_jobs(connection)
if not len(jobs):
print None found, or my jobs were marked by another
Re: [GENERAL] FAQ correction for Windows 2000/XP
Alvaro Herrera wrote: Bruce Momjian escribió: Dan Dascalescu wrote: I'd like to submit a correction for question 2.1) How do I setup a datasource? in the FAQ. The existing text reads: For Windows, use the ODBC Administrator in Control Panel. Here you can add, modify, or delete data sources. On Windows XP, however, there is no Control Panel applet for ODBC management. The answer should read: Go to Programs - Administrative Tools - Data Sources and Add the PostgreSQL Unicode driver. Uh, I am not sure where you saw this FAQ but this is not an FAQ we manage. Where did you see it? http://psqlodbc.projects.postgresql.org/faq.html#2.1 that is still incorrect because very few computers will have the display Administrative tools in start menu turned on. If the user has the new XP style start menu turned on the Reading should be Start - Control Panel- Administrate Tools - Data Sources (ODBC) if the User has Classic Start menu turned on the reading is Go to Start- Settings - Control Panel - Administrative Tools - Data Sources (ODBC) and off course both are wrong if users don't have classic view turned on in control panel Another option is Start - Run - type in odbcad32.exe minus the quotes then click OK button. Its early still given time i could come up with a couple more ways to get to the ODBC admin tool I don't have a clue about Vista sense i have avoided it like the plague.
[GENERAL] how to found a variable is in a aggregation or not?
I want to check a variable is in a aggregattion or not, so I create a
function as below:
create or replace function anytest(val text) returns boolean as $$
begin
perform 1 where quote_literal(val) in ('hello', 'world', 'test');
if not found then
return false;
else
return true;
end if;
end;
$$ language plpgsql;
but when I used, I got the result below, why?
test=# select anytest('world111');
anytest
-
f
(1 row)
test=# select anytest('world');
anytest
-
f
(1 row)
any help is appreciated.
regards,
Zy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to found a variable is in a aggregation or not?
Hello
in this case you must not use quoting
postgres=# create or replace function anytest(val text) returns boolean as $$
begin
perform 1 where val in ('hello', 'world', 'test');
if not found then
return false;
else
return true;
end if;
end;
$$ language plpgsql;
CREATE FUNCTION
Time: 3,342 ms
postgres=# select anytest('hello');
anytest
-
t
(1 row)
Time: 42,034 ms
postgres=# select anytest('helloa');
anytest
-
f
(1 row)
Time: 0,468 ms
postgres=#
you have to use quoting only together dynamic sql, etc EXECUTE statement
regards
Pavel Stehule
2008/7/15 Yi Zhao [EMAIL PROTECTED]:
I want to check a variable is in a aggregattion or not, so I create a
function as below:
create or replace function anytest(val text) returns boolean as $$
begin
perform 1 where quote_literal(val) in ('hello', 'world', 'test');
if not found then
return false;
else
return true;
end if;
end;
$$ language plpgsql;
but when I used, I got the result below, why?
test=# select anytest('world111');
anytest
-
f
(1 row)
test=# select anytest('world');
anytest
-
f
(1 row)
any help is appreciated.
regards,
Zy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Referential integrity vulnerability in 8.3.3
Hello community
There is an oddity (or a bug) in situation with returning null before
delete trigger and referential integrity in PG 8.3.3. I tryed to find
a solution in Google and PG documentation and have noticed nothing
useful.
Let's start from tables creation.
CREATE TABLE table1
(
id serial NOT NULL,
field1 text,
CONSTRAINT table1_pk PRIMARY KEY (id)
);
CREATE TABLE table2
(
id serial NOT NULL,
table1_id integer,
CONSTRAINT table2_pk PRIMARY KEY (id),
CONSTRAINT table2_fk1 FOREIGN KEY (table1_id)
REFERENCES table1 (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
);
Well.. Second one refrences first one and has to be updated and
deleted cascaded. Next create before delete trigger on tabe2 allways
returning null.
CREATE OR REPLACE FUNCTION tr_stop()
RETURNS trigger AS
$BODY$begin
return null;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER tr_stop
BEFORE DELETE
ON table2
FOR EACH ROW
EXECUTE PROCEDURE tr_stop();
Inserting three rows into table1
insert into table1 (id, field1) values (1, 'qqq');
insert into table1 (id, field1) values (2, 'www');
insert into table1 (id, field1) values (3, 'eee');
and refer to them from table2.
insert into table2 (id, table1_id) values (1, 1);
insert into table2 (id, table1_id) values (2, 2);
insert into table2 (id, table1_id) values (3, 3);
Now comming to a head. As I supposed earlier, deletion from table1 has
to be prevented by referential integrity when the trigger prevents
deletion of refered row from table2. But it doesn't.
delete from table1;
It deletes all rows from table1 and doesn't touch rows from table2.
select * from table1
id | field1
+
(0 rows)
select * from table2 where not exists(select 1 from table1 where id =
table2.table1_id)
id | table1_id
+---
1 | 1
2 | 2
3 | 3
(3 rows)
Will you explain me please why PG behave so cos IMHO it's a bit
illogical. Thanx.
p.s. Some info from pg_trigger below
select c.relname, t.* from pg_class c left join pg_trigger t on
t.tgrelid = c.oid
where relname in ('table1', 'table2') order by relname
relname | tgrelid |tgname | tgfoid |
tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid |
tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr |
tgargs
-+--+---+--++---++--+---+--+--++-++
table1 | 55880268 | RI_ConstraintTrigger_55880305 | 1646 |
9 | O | t | table2_fk1 | 55880296 |
55880302 | f| f | 0 ||
table1 | 55880268 | RI_ConstraintTrigger_55880306 | 1647 |
17 | O | t | table2_fk1 | 55880296 |
55880302 | f| f | 0 ||
table2 | 55880296 | tr_stop |
55881180 | 11 | O | f | |
0 |0 | f| f | 0 |
|
table2 | 55880296 | RI_ConstraintTrigger_55880303 | 1644 |
5 | O | t | table2_fk1 | 55880268 |
55880302 | f| f | 0 ||
table2 | 55880296 | RI_ConstraintTrigger_55880304 | 1645 |
17 | O | t | table2_fk1 | 55880268 |
55880302 | f| f | 0 ||
(5 rows)
--
Regards,
Sergey Konoplev
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Referential integrity vulnerability in 8.3.3
Sergey Konoplev wrote: There is an oddity (or a bug) in situation with returning null before delete trigger and referential integrity in PG 8.3.3. I tryed to find a solution in Google and PG documentation and have noticed nothing useful. [snip] CREATE OR REPLACE FUNCTION tr_stop() RETURNS trigger AS $BODY$begin return null; end;$BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER tr_stop BEFORE DELETE ON table2 FOR EACH ROW EXECUTE PROCEDURE tr_stop(); [snip] Now comming to a head. As I supposed earlier, deletion from table1 has to be prevented by referential integrity when the trigger prevents deletion of refered row from table2. But it doesn't. [snip] Will you explain me please why PG behave so cos IMHO it's a bit illogical. Thanx. Your trigger doesn't prevent deletion, it just skips the row(s) in question from being affected. Raise an exception if you want to abort the transaction. See the manual - triggers chapter and plpgsql chapter for more details. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backing up and deleting a database.
In response to Andrew Maclean [EMAIL PROTECTED]: We have a database that grows in size quite quickly. Of course we backup nightly and keep a weeks worth of data However we need to keep a few months data online, but the rest can be archived as it will be unlikley that it will be used again. As I see it we can: 1) Run a query to drop/delete old data, the downside here is that we lose it. 2) Stop the database (this is important because clients are writing to it), back it up, delete it and recreate the database. Has anyone done this? Do they have a script for this? I'm confused. If you can't back up the data because clients are writing to it, then it must be interesting to those clients, so why are you able to delete it? Would a script that does the following work: 1) BEGIN; CREATE TABLE stage_archive AS (SELECT * FROM ??? WHERE [some where clause to identify old records]); DELETE FROM ??? WHERE [same where clause]; COMMIT; 2) COPY stage_archive TO 'some_file.sql' 3) Back up or otherwise archive some_file.sql 4) DROP TABLE stage_archive; -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unicode database on non-unicode operating system
Hi, I have a PostgreSQL 8.0.3 running on an older debian server and have some problems with unicode databases and character conversions. First up, some backgrund info about my server and installation: test=# \set VERSION = 'PostgreSQL 8.0.3 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13)' AUTOCOMMIT = 'on' VERBOSITY = 'default' PROMPT1 = '%/%R%# ' PROMPT2 = '%/%R%# ' PROMPT3 = ' ' HISTSIZE = '500' LASTOID = '0' DBNAME = 'test' USER = 'postgres' HOST = '/var/run/postgresql' PORT = '5432' ENCODING = 'UNICODE' test=# \! uname -a Linux xx 2.4.27-2-686-smp #1 SMP XX i686 GNU/Linux test=# \! locale LANG=POSIX LC_CTYPE=POSIX LC_NUMERIC=POSIX LC_TIME=POSIX LC_COLLATE=POSIX LC_MONETARY=POSIX LC_MESSAGES=POSIX LC_PAPER=POSIX LC_NAME=POSIX LC_ADDRESS=POSIX LC_TELEPHONE=POSIX LC_MEASUREMENT=POSIX LC_IDENTIFICATION=POSIX LC_ALL= My problem is, that the lowercase versions of non-ascii characters are broken. Specifically I found, that when lower() is invoked on a text with non-ascii characters, the operating system's locale is used for converting each octet in the string to lowercase in stead of using the locale of the database to convert each character in the string to lowercase. This caused the danish lower case o with slash ø, which in unicode is represented as the latin1-readable octets ø, to be converted to the latin1-readable octets ã¸, which then in turn was (tried) to be interpreted as a unicode character - but the octects 㸠does not represent a unicode character in utf8. The lower case version of ø is of course just itself. To get around this problem, I had to create a function ulower: create or replace function ulower(text) returns text as 'begin return convert(lower(convert($1,''utf8'',''latin1'')),''latin1'',''utf8''); end;' language plpgsql immutable; Not a very nice solution and it of course only works for latin1-compatible utf8-encoded strings. First up, I would like to avoid this whole issue. How could this be circumvented, any settings I can flick around? Then I tried to apply this immutable function as a functional index on a varchar: test# create index mytable_mycolumn_lower_idx on mytable(ulower(mycolumn)); ERROR: could not convert UTF-8 character 0x00e2 to ISO8859-1 I had a lot of data in the table before creating this index, and apparently one of the rows contained unicode character U+00E2 - which is latin small letter a with circumflex = â. This is a perfectly legal latin1-character (as any Unicode character below U+0100 is). My second question is then, why did it fail to convert this character to latin1? Thanks in advance for any help Regards, Morten Barklund Head of Development TBWA\
Re: [GENERAL] Referential integrity vulnerability in 8.3.3
Yes it is. But it the way to break integrity cos rows from table2 still refer to deleted rows from table1. So it conflicts with ideology isn't it? On Tue, Jul 15, 2008 at 4:00 PM, Richard Huxton [EMAIL PROTECTED] wrote: Sergey Konoplev wrote: There is an oddity (or a bug) in situation with returning null before delete trigger and referential integrity in PG 8.3.3. I tryed to find a solution in Google and PG documentation and have noticed nothing useful. [snip] CREATE OR REPLACE FUNCTION tr_stop() RETURNS trigger AS $BODY$begin return null; end;$BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER tr_stop BEFORE DELETE ON table2 FOR EACH ROW EXECUTE PROCEDURE tr_stop(); [snip] Now comming to a head. As I supposed earlier, deletion from table1 has to be prevented by referential integrity when the trigger prevents deletion of refered row from table2. But it doesn't. [snip] Will you explain me please why PG behave so cos IMHO it's a bit illogical. Thanx. Your trigger doesn't prevent deletion, it just skips the row(s) in question from being affected. Raise an exception if you want to abort the transaction. See the manual - triggers chapter and plpgsql chapter for more details. -- Richard Huxton Archonet Ltd -- Regards, Sergey Konoplev -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unicode database on non-unicode operating system
Am Dienstag, 15. Juli 2008 schrieb Morten Barklund: My problem is, that the lowercase versions of non-ascii characters are broken. Specifically I found, that when lower() is invoked on a text with non-ascii characters, the operating system's locale is used for converting each octet in the string to lowercase in stead of using the locale of the database to convert each character in the string to lowercase. This caused the danish lower case o with slash ø, which in unicode is represented as the latin1-readable octets ø, to be converted to the latin1-readable octets ã¸, which then in turn was (tried) to be interpreted as a unicode character - but the octects 㸠does not represent a unicode character in utf8. The lower case version of ø is of course just itself. This means you have mismatching server encodings and locales configured. Check SHOW lc_collate and SHOW server_encoding, and then pick a combination that is compatible. This will probably mean you have to reinitdb. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Referential integrity vulnerability in 8.3.3
Sergey Konoplev wrote: Yes it is. But it the way to break integrity cos rows from table2 still refer to deleted rows from table1. So it conflicts with ideology isn't it? Yes, but I'm not sure you could have a sensible behaviour-modifying BEFORE trigger without this loophole. Don't forget, ordinary users can't work around this - you need suitable permissions. You could rewrite PG's foreign-key code to check the referencing table after the delete is supposed to have taken place, and make sure it has. That's going to halve the speed of all your foreign-key checks though. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to found a variable is in a aggregation or not?
it's works,
thanks a lot!
regards,
Yi
On Tue, 2008-07-15 at 13:30 +0200, Pavel Stehule wrote:
Hello
in this case you must not use quoting
postgres=# create or replace function anytest(val text) returns boolean as $$
begin
perform 1 where val in ('hello', 'world', 'test');
if not found then
return false;
else
return true;
end if;
end;
$$ language plpgsql;
CREATE FUNCTION
Time: 3,342 ms
postgres=# select anytest('hello');
anytest
-
t
(1 row)
Time: 42,034 ms
postgres=# select anytest('helloa');
anytest
-
f
(1 row)
Time: 0,468 ms
postgres=#
you have to use quoting only together dynamic sql, etc EXECUTE statement
regards
Pavel Stehule
2008/7/15 Yi Zhao [EMAIL PROTECTED]:
I want to check a variable is in a aggregattion or not, so I create a
function as below:
create or replace function anytest(val text) returns boolean as $$
begin
perform 1 where quote_literal(val) in ('hello', 'world', 'test');
if not found then
return false;
else
return true;
end if;
end;
$$ language plpgsql;
but when I used, I got the result below, why?
test=# select anytest('world111');
anytest
-
f
(1 row)
test=# select anytest('world');
anytest
-
f
(1 row)
any help is appreciated.
regards,
Zy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unicode database on non-unicode operating system
Hi Peter, Thanks for the hint. I can see that lc_collate (sorting) and lc_ctype (lower-upper conversion) is set to en_DK and I guess that default encoding for en_DK is iso88591 or maybe windows1252. Thus my server should have been initialized with en_DK.utf8 or? How do I find out what the default encoding for the locale en_DK is? I can see, that normally one would sub-specify this by either adding .iso88591 or .utf8, but is windows1252 then default? Because it is clear, that en_DK includes the proper rules for upper-lower conversion of Danish special characters as I when converting from UTF-8 to ISO 8859-1 can use upper() and lower() as expected. And Danish special characters have the same code points in latin1 and windows1252. I am not able to reinitdb, as many other databases are running, which might be affected negatively. This means, that even though my database is created WITH ENCODING 'unicode', it is in fact broken as the locale does not fully support unicode string handling? I wanted to use Unicode, as I expected non-latin1 characters, but this actually means, that if I had any such, some string functions would not work at all. Regards, Morten Barklund -Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2008 2:33 PM To: pgsql-general@postgresql.org Cc: Morten Barklund Subject: Re: [GENERAL] Unicode database on non-unicode operating system Am Dienstag, 15. Juli 2008 schrieb Morten Barklund: My problem is, that the lowercase versions of non-ascii characters are broken. Specifically I found, that when lower() is invoked on a text with non-ascii characters, the operating system's locale is used for converting each octet in the string to lowercase in stead of using the locale of the database to convert each character in the string to lowercase. This caused the danish lower case o with slash ø, which in unicode is represented as the latin1-readable octets ø, to be converted to the latin1-readable octets ã¸, which then in turn was (tried) to be interpreted as a unicode character - but the octects 㸠does not represent a unicode character in utf8. The lower case version of ø is of course just itself. This means you have mismatching server encodings and locales configured. Check SHOW lc_collate and SHOW server_encoding, and then pick a combination that is compatible. This will probably mean you have to reinitdb. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FAQ correction for Windows 2000/XP
Alvaro Herrera wrote: Bruce Momjian escribi?: Dan Dascalescu wrote: I'd like to submit a correction for question 2.1) How do I setup a datasource? in the FAQ. The existing text reads: For Windows, use the ODBC Administrator in Control Panel. Here you can add, modify, or delete data sources. On Windows XP, however, there is no Control Panel applet for ODBC management. The answer should read: Go to Programs - Administrative Tools - Data Sources and Add the PostgreSQL Unicode driver. Uh, I am not sure where you saw this FAQ but this is not an FAQ we manage. Where did you see it? http://psqlodbc.projects.postgresql.org/faq.html#2.1 OK, thanks, forwarding to Dave Page, the FAQ maintainer. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.3 Complie issue
Harvey, Allan AC wrote:
Fixed that, how about this
echo '{ global:' exports.list
gawk '/^[^#]/ {printf %s;\n,$1}' exports.txt exports.list
echo ' local: *; };' exports.list
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-fno-strict-aliasing -fpic -shared -Wl,-soname,libpq.so.5
-Wl,--version-script=exports.list fe-auth.o fe-connect.o fe-exec.o fe-misc.o
fe-print.o fe-lobj.o fe-protocol2.o fe-protocol3.o pqexpbuffer.o pqsignal.o
fe-secure.o md5.o ip.o wchar.o encnames.o noblock.o pgstrcasecmp.o thread.o
strlcpy.o -L../../../src/port -lcrypt -Wl,-rpath,'/removeExt2/pgsql8.3/lib'
-o libpq.so.5.1
/usr/i386-slackware-linux/bin/ld:exports.list:1: parse error in VERSION script
collect2: ld returned 1 exit status
Old Slackware? If you really want to compile there, I think it should
work by just removing the -Wl,--version-script param from the link line.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to found a variable is in a aggregation or not?
so this code is little bit ugly
you can write faster code
create or replace function anytest(val text)
returns boolean as $$
begin
return val in ('hello', 'world','test');
end;
$$ language plpgsql immutable strict;
Pavel
2008/7/15 Yi Zhao [EMAIL PROTECTED]:
I want to check a variable is in a aggregattion or not, so I create a
function as below:
create or replace function anytest(val text) returns boolean as $$
begin
perform 1 where quote_literal(val) in ('hello', 'world', 'test');
if not found then
return false;
else
return true;
end if;
end;
$$ language plpgsql;
but when I used, I got the result below, why?
test=# select anytest('world111');
anytest
-
f
(1 row)
test=# select anytest('world');
anytest
-
f
(1 row)
any help is appreciated.
regards,
Zy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unicode database on non-unicode operating system
Am Dienstag, 15. Juli 2008 schrieb Morten Barklund: I can see that lc_collate (sorting) and lc_ctype (lower-upper conversion) is set to en_DK and I guess that default encoding for en_DK is iso88591 or maybe windows1252. It is ISO-8859-1. There is no support for Windows charmaps on Linux. Thus my server should have been initialized with en_DK.utf8 or? Yes, or you should have chosen a different encoding (LATIN1 in your case) when creating the database. How do I find out what the default encoding for the locale en_DK is? $ LC_ALL=en_DK locale charmap ISO-8859-1 Note that this is not the default encoding, it is the *only* encoding supported by that locale. I can see, that normally one would sub-specify this by either adding .iso88591 or .utf8, but is windows1252 then default? It might be reasonable to use the .iso88591 or .utf8 suffixes if you want to be explicit, but the unsuffixed locale name is usually just an alias for one of these. I am not able to reinitdb, as many other databases are running, which might be affected negatively. This means, that even though my database is created WITH ENCODING 'unicode', it is in fact broken as the locale does not fully support unicode string handling? Yes. If you can't reinitdb, then you should recreate the database with encoding LATIN1. This won't allow all Unicode characters, obviously, but at least you get proper behavior for the Danish characters that you need. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Referential integrity vulnerability in 8.3.3
Yes it is. But it the way to break integrity cos rows from table2 still refer to deleted rows from table1. So it conflicts with ideology isn't it? Yes, but I'm not sure you could have a sensible behaviour-modifying BEFORE trigger without this loophole. Don't forget, ordinary users can't work around this - you need suitable permissions. You could rewrite PG's foreign-key code to check the referencing table after the delete is supposed to have taken place, and make sure it has. That's going to halve the speed of all your foreign-key checks though. I'm not sure I've understood you right, sorry. Does rewrite PG's foreign-key code mean DDL? If it does how could I do this? -- Regards, Sergey Konoplev -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Psql crashes with Segmentation fault on copy from
Tom Lane wrote: I wrote: Francisco Reyes [EMAIL PROTECTED] writes: On 3:09 pm 05/28/08 Gregory Stark [EMAIL PROTECTED] wrote: Does it really have a COPY command at the beginning? Are you really doing \i data/usb_t_60M.sql or were you trying to do a copy from this file? Argh..That's it. When I re-organized the scripts I must have taken the copy command from the top of the data file and did not put a 'copy from' in the calling script. Hmm ... even so, it shouldn't have crashed, it should at worst have given you an out-of-memory error message. Hmm, I see the problem: the pqexpbuffer code maxes out at INT_MAX bytes in the string buffer, which would be all right except that it has no good way to report the error and so the input is just getting truncated at that length. But then what happens is that pqPuts computes strlen(s) + 1, which is still all right because size_t is 64 bits on this machine, and passes that to pqPutMsgBytes, which computes conn-outMsgEnd + len *and smashes that to int*. So the value passed to pqCheckOutBufferSpace is negative, and the latter falsely concludes there's enough space in the buffer, and then the memcpy goes boom. A minimum solution would be to make pqCheckOutBufferSpace deal in size_t not int, but I have a feeling there are a lot of other similar gotchas when running this code on a 64-bit machine. We use int arithmetic an awful lot for stuff that probably should be size_t or ssize_t ... I assume this is not a TODO item. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgsql-advocacy] [pdxpug] Pg booth staffing at OSCON
Daniel, I'm starting a new job this week, but they said I can get the OSCON days off so I can fill in the booth for many of the empty spots. I don't want to do the booth during the keynotes (and what would be the point anyways no one will be in the exhibitor hall), and I'd kinda like to schedule it around when things of interest are happening in OSCAMP, but who knows what that schedule will be until the conference actually starts. Well, you can obviously get into OSCAMP and FOSSCoach and the BOFs and the other free events. Not that I'd be promoting such a thing (as an OSCON committee member), but if there's *a* specific session you want to attend, you can probably persuade one of the several PostgreSQL speakers to loan you their badge. If you want a full pass, I have access to substantial discount codes, although you already missed the early bird discount. -- Josh Berkus PostgreSQL @ Sun San Francisco -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FAQ correction for Windows 2000/XP
On Tue, Jul 15, 2008 at 2:34 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Alvaro Herrera wrote: Bruce Momjian escribi?: Dan Dascalescu wrote: I'd like to submit a correction for question 2.1) How do I setup a datasource? in the FAQ. The existing text reads: For Windows, use the ODBC Administrator in Control Panel. Here you can add, modify, or delete data sources. On Windows XP, however, there is no Control Panel applet for ODBC management. The answer should read: Go to Programs - Administrative Tools - Data Sources and Add the PostgreSQL Unicode driver. Uh, I am not sure where you saw this FAQ but this is not an FAQ we manage. Where did you see it? http://psqlodbc.projects.postgresql.org/faq.html#2.1 OK, thanks, forwarding to Dave Page, the FAQ maintainer. hmm, I haven't maintained that in some years, but I do still have access I think. That said, the FAQ isn't exactly wrong - Control Panel is the standard way to get to Administrative Tools which contains the applet. I'll see if I can add an appropriate hint. Regards, Dave. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unicode database on non-unicode operating system
Hi Peter, Thank you once again. That cleared up a lot of confusion for me and my co-workers and the next server set up will be with unicode and en_DK.utf8 to ensure consistency. Regards, Morten Barklund -Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2008 3:50 PM To: pgsql-general@postgresql.org Cc: Morten Barklund Subject: Re: [GENERAL] Unicode database on non-unicode operating system Am Dienstag, 15. Juli 2008 schrieb Morten Barklund: I can see that lc_collate (sorting) and lc_ctype (lower-upper conversion) is set to en_DK and I guess that default encoding for en_DK is iso88591 or maybe windows1252. It is ISO-8859-1. There is no support for Windows charmaps on Linux. Thus my server should have been initialized with en_DK.utf8 or? Yes, or you should have chosen a different encoding (LATIN1 in your case) when creating the database. How do I find out what the default encoding for the locale en_DK is? $ LC_ALL=en_DK locale charmap ISO-8859-1 Note that this is not the default encoding, it is the *only* encoding supported by that locale. I can see, that normally one would sub-specify this by either adding .iso88591 or .utf8, but is windows1252 then default? It might be reasonable to use the .iso88591 or .utf8 suffixes if you want to be explicit, but the unsuffixed locale name is usually just an alias for one of these. I am not able to reinitdb, as many other databases are running, which might be affected negatively. This means, that even though my database is created WITH ENCODING 'unicode', it is in fact broken as the locale does not fully support unicode string handling? Yes. If you can't reinitdb, then you should recreate the database with encoding LATIN1. This won't allow all Unicode characters, obviously, but at least you get proper behavior for the Danish characters that you need. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] C-procedure crashed in Postgres 8.3.3 when using 'text' variable (WinXP)
el dorado [EMAIL PROTECTED] writes: I'd like to write C-function returning text for using in PG 8.3.3. (WinXP SP2) For compilation I use MinGW-5.1.4 (gcc 3.4.5),MSYS-1.0.10. Was the Postgres server you're using built the same way? I seem to recall some incompatibilities between MinGW and MSVC builds. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgsql-advocacy] [pdxpug] Pg booth staffing at OSCON
Well, you can obviously get into OSCAMP and FOSSCoach and the BOFs and the other free events. Not that I'd be promoting such a thing (as an OSCON committee member), but if there's *a* specific session you want to attend, you can probably persuade one of the several PostgreSQL speakers to loan you their badge. No offense, but in the years that I have been going to OSCON I've never had anywhere near enough money to buy a real pass to OSCON. Every year it has been volunteering for a booth, or attending OSCAMP. Last year I was able to afford Ubuntu Live through one of the more extreme discount codes, but that is it. There are lots of people who participate as much as they can by way of volunteering, and I am one of them. We help make the conference happen so please treat us with respect even if we can't afford to pay are way in. As a committee members you are technically a volunteer with a free pass yourself! I would have really loved to attend as a regular conference goer, but that just isn't financially possible for me. I almost got one this year because a friend on the committee can't come this year, but apparently committee passes aren't transferable. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inaccurate row count estimation
Thanks for the reply, Tom. After tracing through this I see that the problem is that we don't have statistics for inheritance trees, and so you're getting a default estimate for the selectivity of the join condition. I might be wrong but I suspect that the inheritance is not the only reason here. If I change the table definitions to: create table pinfo_p00 ( pid integer, constraint pk_pinfo_p00 primary key (pid), constraint cc_pinfo_p00_pid check(pid 0 and pid 10) ); create table pinfo_p01 ( pid integer, constraint pk_pinfo_p01 primary key (pid), constraint cc_pinfo_p01_pid check(pid = 10 and pid 20) ); and create a view pinfo, or just do a query with subselect: explain analyze select * from contacts c left join ( select * from pinfo_p00 union all select * from pinfo_p01 ) pi on (pi.pid = c.cpid) where c.pid = 200 ; the row-count assessment doesn't seem to be different: QUERY PLAN Nested Loop Left Join (cost=4.56..514.25 rows=3896 width=16) (actual time=0.125..3.976 rows=40 loops=1) Join Filter: (pinfo_p00.pid = c.cpid) - Bitmap Heap Scan on contacts c (cost=4.56..100.34 rows=39 width=12) (actual time=0.069..0.421 rows=40 loops=1) Recheck Cond: (pid = 200) - Bitmap Index Scan on ix_contacts_pid (cost=0.00..4.55 rows=39 width=0) (actual time=0.042..0.042 rows=40 loops=1) Index Cond: (pid = 200) - Append (cost=0.00..10.59 rows=2 width=4) (actual time=0.033..0.061 rows=1 loops=40) - Index Scan using pk_pinfo_p00 on pinfo_p00 (cost=0.00..5.29 rows=1 width=4) (actual time=0.011..0.015 rows=0 loops=40) Index Cond: (pinfo_p00.pid = c.cpid) - Index Scan using pk_pinfo_p01 on pinfo_p01 (cost=0.00..5.29 rows=1 width=4) (actual time=0.012..0.015 rows=0 loops=40) Index Cond: (pinfo_p01.pid = c.cpid) Total runtime: 4.341 ms It scares me a bit as it seems that innocent-looking combination of union's and join's could destroy the subsequent plan completely.
Re: [GENERAL] Installing PostgreSQL without using CygWin
Better to go here: http://www.postgresql.org/ftp/binary/v8.3.3/win32/ and get the latest version On Mon, Jul 14, 2008 at 11:01 PM, Dann Corbit [EMAIL PROTECTED] wrote: From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of ken andrew Sent: Sunday, July 13, 2008 10:27 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Installing PostgreSQL without using CygWin Hi, I would like to know if there is a way to install PostgreSQL in Windows NT Server 2003 without installing CygWin. The client insists on not installing CygWin. The current version of the Installer installs a native build. Cygwin has not been used in the standard Windows build for a long time. Go here: http://www.postgresql.org/ftp/binary/v8.3.1/win32/ Get this: http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fbinary%2Fv8.3.1%2Fwin32%2Fpostgresql-8.3.1-1.zip -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Referential integrity vulnerability in 8.3.3
On Tue, Jul 15, 2008 at 06:02:27PM +0400, Sergey Konoplev wrote: Yes it is. But it the way to break integrity cos rows from table2 still refer to deleted rows from table1. So it conflicts with ideology isn't it? Yes, but I'm not sure you could have a sensible behaviour-modifying BEFORE trigger without this loophole. Don't forget, ordinary users can't work around this - you need suitable permissions. You could rewrite PG's foreign-key code to check the referencing table after the delete is supposed to have taken place, and make sure it has. That's going to halve the speed of all your foreign-key checks though. I'm not sure I've understood you right, sorry. Does rewrite PG's foreign-key code mean DDL? If it does how could I do this? The code you posted is a clear case of doing things wrong deliberately. In order to prevent this error, you would need to rewrite large parts of Postgres's code which checks referential integrity, and there would still be things that deliberately wrong DDL, triggers, rules, etc. could do. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Referential integrity vulnerability in 8.3.3
Sergey Konoplev wrote: Yes it is. But it the way to break integrity cos rows from table2 still refer to deleted rows from table1. So it conflicts with ideology isn't it? Yes, but I'm not sure you could have a sensible behaviour-modifying BEFORE trigger without this loophole. Don't forget, ordinary users can't work around this - you need suitable permissions. You could rewrite PG's foreign-key code to check the referencing table after the delete is supposed to have taken place, and make sure it has. That's going to halve the speed of all your foreign-key checks though. I'm not sure I've understood you right, sorry. Does rewrite PG's foreign-key code mean DDL? If it does how could I do this? No, I was saying that to change this you'd have to alter PostgreSQL's source-code. You'd also have the issue of what to do with other triggers. You'd need some priority level setting to allow some triggers to override other triggers, but not the reverse. If you really want to suppress deletion from table2 while enforcing deletion via foreign-key you're best off with something like: CREATE OR REPLACE FUNCTION fktrigfn() RETURNS TRIGGER AS $$ BEGIN PERFORM 1 FROM table1 WHERE a = OLD.aref; IF FOUND THEN RAISE NOTICE 'aborting delete for %', OLD.aref; RETURN NULL; ELSE RAISE NOTICE 'allowing delete for %', OLD.aref; RETURN OLD; END IF; END; $$ LANGUAGE plpgsql; That should be OK, because the row should always be marked as removed from table1 before the delete cascades. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Out of memry with large result set
Try copy (query) to stdout. For me, psql sits at 4.9mb ram on a 3x10^16 row query. klint. Thanks Klint. Can I use a 'copy to' for a query ? I thought I can only do 'copy table to stdout' I will do some tests tomorrow and keep you inform. Olivier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] vacuum taking an unusually long time
None of these values have changed recently. The values are: vacuum_cost_delay = 10ms vacuum_cost_limit = 200 Are there any other values I should be looking at? The longest running vacuum has been running more than 6 days at this point. Thanks, Mason On Mon, Jul 14, 2008 at 4:39 PM, Scott Marlowe [EMAIL PROTECTED] wrote: On Mon, Jul 14, 2008 at 3:08 PM, Mason Hale [EMAIL PROTECTED] wrote: Vacuum operations on several tables are taking much longer than they previously were. We currently have 3 autovacuum processes that have been running more than 3 days each. The tables are large (between 40 and 90GB each). Postgresql version is 8.3.1 maintenance_work_mem is 512MB (on a 32GB server). Any ideas what would make vacuum take so long? What can I do to speed things up? Have you adjusted your vacuum / autovacuum cost parameters up? that will certainly slow down vacuums. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump
Is there a GUI for pg_dump??? Bob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump
On 15/07/2008 19:31, Bob Pawley wrote: Is there a GUI for pg_dump??? Yep - it's called PgAdmin!! :-) Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump
Is there a GUI for pg_dump??? Bob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] query optimization
Hello:
I need your kind assistance to debug an optimization issue.
The following two select statements in my book are almost identical.
One does a lookup for security type 'CFD' and
the other does the same lookup except for security 'OP'. When run with
'CFD' the query never returns.
When run with 'OP' the results return almost instantly. If I hard code
'CFD' to be 5 which is the securitytypekey,
the query runs instantly.
The code for getsectypekey() is below. Please note SECURITYTYPE
contains only 28 rows.
Why would these queries run so differently?
Many thanks for taking the time to look at this issue.
KD
select sec.*
from security sec , positions_gsco
where positions_gsco.securitykey is NULL and
upper(substring(productid,1,3)) = 'CFD' and
upper(positions_gsco.producttype) = 'OP' and
getsectypekey('CFD') = sec.securitytypekey and
positions_gsco.taskrunkey = 359 and
positions_gsco.issuecurrency = sec.securityissuecurrISO and
positions_gsco.strikeprice = sec.strikeprice and
positions_gsco.expirationdate = sec.expirationdate and
( positions_gsco.underlyingisin = sec.underlyingisin or
positions_gsco.underlyingcusip = sec.underlyingcusip or
positions_gsco.underlyingbloombergticker = sec.
underlyingbloomberg ) ;
EXPLAIN
Nested Loop (cost=0.00..2829.87 rows=1 width=374)
Join Filter: ((positions_gsco.issuecurrency =
(sec.securityissuecurriso)::bpchar) AND (positions_gsco.strikeprice =
sec.strikeprice) AND (positions_gsco.expirationdate =
sec.expirationdate) AND ((positions_gsco.underlyingisin =
(sec.underlyingisin)::bpchar) OR (positions_gsco.underlyingcusip =
(sec.underlyingcusip)::bpchar) OR
(positions_gsco.underlyingbloombergticker = sec.underlyingbloomberg)))
- Seq Scan on positions_gsco (cost=0.00..2310.40 rows=1 width=72)
Filter: ((securitykey IS NULL) AND
(upper(substring((productid)::text, 1, 3)) = 'CFD'::text) AND
(upper((producttype)::text) = 'OP'::text) AND (taskrunkey = 359))
- Seq Scan on security sec (cost=0.00..504.52 rows=598
width=374)
Filter: (getsectypekey('CFD'::bpchar) = securitytypekey)
** ** ** ** ** ** **
** ** ** ** ** ** **
select sec.*
from security sec , positions_gsco
where positions_gsco.securitykey is NULL and
upper(substring(productid,1,3)) != 'CFD' and
upper(positions_gsco.producttype) = 'OP' and
getsectypekey('OP') = sec.securitytypekey and
positions_gsco.taskrunkey = 359 and
positions_gsco.issuecurrency = sec.securityissuecurrISO and
positions_gsco.putcallind = sec.put_call and
positions_gsco.strikeprice = sec.strikeprice and
positions_gsco.expirationdate = sec.expirationdate and
( positions_gsco.underlyingisin = sec.underlyingisin or
positions_gsco.underlyingcusip = sec.underlyingcusip or
positions_gsco.underlyingbloombergticker =
sec.underlyingbloomberg);
EXPLAIN
Hash Join (cost=514.99..2861.41 rows=1 width=374)
Hash Cond: ((positions_gsco.issuecurrency =
(sec.securityissuecurriso)::bpchar) AND (positions_gsco.putcallind =
sec.put_call) AND (positions_gsco.expirationdate = sec.expirationdate))
Join Filter: ((positions_gsco.strikeprice = sec.strikeprice) AND
((positions_gsco.underlyingisin = (sec.underlyingisin)::bpchar) OR
(positions_gsco.underlyingcusip = (sec.underlyingcusip)::bpchar) OR
(positions_gsco.underlyingbloombergticker = sec.underlyingbloomberg)))
- Seq Scan on positions_gsco (cost=0.00..2310.40 rows=16
width=79)
Filter: ((securitykey IS NULL) AND
(upper(substring((productid)::text, 1, 3)) 'CFD'::text) AND
(upper((producttype)::text) = 'OP'::text) AND (taskrunkey = 359))
- Hash (cost=504.52..504.52 rows=598 width=374)
- Seq Scan on security sec (cost=0.00..504.52 rows=598
width=374)
Filter: (getsectypekey('OP'::bpchar) = securitytypekey)
** ** ** ** ** ** **
** ** ** ** ** ** **
CREATE OR REPLACE FUNCTION getsectypekey(sectype_in bpchar)
RETURNS integer AS
$BODY$
declare
sectypekey integer;
begin
select securitytypekey into sectypekey
from securitytype
where position (upper('|' || sectype_in || '|' ) in
upper(securitytypeaka) ) 0;
--
-- did not find a type above
if sectypekey is NULL then
select securitytypekey into sectypekey
from securitytype
where upper(securitytypeshort) = 'UNKNOWN';
end if;
return sectypekey;
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION getsectypekey(sectype_in bpchar) OWNER TO postgres;
Kevin Duffy
Re: [GENERAL] optimizer ignoring primary key and doing sequence scan
On Tue, Jul 15, 2008 at 12:19 AM, Edoardo Panfili [EMAIL PROTECTED] wrote: Scott Marlowe ha scritto: On Mon, Jul 14, 2008 at 1:54 PM, Chris Hoy [EMAIL PROTECTED] wrote: Hi I have a number of tables in my database where the queries appear to ignoring the primary key and doing a seq scan instead, however other tables appear to be fine. I can see any difference between them. Is their any way of determination why the otimizer isn't picking up the primary key? Version 8.3.3 windows An example of a non working table is: select * from industries where industryid = 1; Seq Scan on industries (cost=0.00..1.02 rows=1 width=116) (actual time=0.011..0.013 rows=1 loops=1) According to this there's only one row in the table. why WOULD postgresql use an index when it can just scan the one row table in a split second. I agree with you that it can depend on the size of the table but where you can read that the table contains only one row? Actually I meant to write one page or block there, not row. But it's the same diff really. 1 Row or 100, if they fit in an 8k block together, pgsql isn't going to use an index to look them up. It kinda knows which block they'll be in ahead of time. I try with my table (39910 rows, no index on column note) explain analyze select * from table where note='single example'; Seq Scan on table (cost=0.00..2458.88 rows=13 width=327) (actual time=10.901..481.896 rows=1 loops=1) Yeah that'll take 481 blocks scanned to get the answer. On the postgres manual I can find Estimated number of rows output by this plan node (Again, only if executed to completion.) regarding the third parameter of the explain Where is my error? I'm not seeing an error, just a possible misunderstanding of pgsql plans queries. Without an index above on note, it has to do a sequential scan there's no index to shorten up the work. The estimated number of rows are how many pgsql thinks it will get back when it runs the query, based on the statistics it has stored from the last time analyze was run. The actual number of rows listed in the explain analyze output is the actual number of rows, not the estimated number... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] C-procedure crashed in Postgres 8.3.3 when using 'text' variable (WinXP)
Tom Lane wrote: el dorado [EMAIL PROTECTED] writes: I'd like to write C-function returning text for using in PG 8.3.3. (WinXP SP2) For compilation I use MinGW-5.1.4 (gcc 3.4.5),MSYS-1.0.10. Was the Postgres server you're using built the same way? I seem to recall some incompatibilities between MinGW and MSVC builds. I'd be surprised if there weren't some given how tricky it is to mix different compilers on Windows, where that usually means mixing C runtimes as well. On any platform it's best to stick to the same C runtime for all libraries to be linked into the same process*. That said, PostgreSQL does appear to use its own allocator for memory passed across library boundaries, and if it doesn't pass any FILE pointers across library boundaries either then that's the worst two problems that arise with a C runtime mismatch taken care of. As far as I know, anyway, and I've had the misfortune to have to dig into it a bit. It seems like a good idea to use the same version of the same compiler that your version of PostgreSQL was built with anyway. If nothing else, I've only taken a cursory look at the code and have little knowledge about it, so there could well be code paths/uses where memory is malloc()'d in one DLL and freed()'d in another, etc. I'm sure there all sorts of other possible wrinkles, and it'd just be easier to stick with the same compiler. You can download Visual Studio 2005 Express Edition for free from MS. * Thankfully UNIXes tend to be sane enough to use the same C library across all C compilers installed (and generally stick to one compiler anyway), so most of us are able to avoid this exciting little complication. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.3 Complie issue
Old Slackware? If you really want to compile there, I think it should work by just removing the -Wl,--version-script param from the link line. Thanks Alvaro, Worked through them. Regression tests show all is OK, save for the handling of Infinity. Don't think that will be a problem 'cause I never go there. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump
-- Original message -- From: Bob Pawley [EMAIL PROTECTED] Is there a GUI for pg_dump??? Bob http://www.pgadmin.org/docs/1.8/backup.html -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgsql-advocacy] [pdxpug] Pg booth staffing at OSCON
Daniel, We help make the conference happen so please treat us with respect even if we can't afford to pay are way in. Huh? How was I disrespectful? Because I suggested discounts? As a committee members you are technically a volunteer with a free pass yourself! Well, I actually have a free pass because I'm a speaker. Presumably you could also get one as a volunteer in other areas like working the speaker room -- I don't know where ORA uses volunteers. The vendor booths are seen as benefitting the vendor (or open source project) and not as benefitting O'Reilly. Remember that ORA doesn't charge for access to the Exhibit Hall. So, if there are specific sessions you want to get into, you can probably manage it, but not I nor Selena nor Gabrielle have access to any full conference passes. I would have really loved to attend as a regular conference goer, but that just isn't financially possible for me. Nor is it for a lot of people. For whatever reason O'Reilly chose the route years ago of having a lot of free and heavily discounted passes for open source luminaries, and charging a lot to the *minority* of attendees who can pay. You can argue whether or not this financial model was a good idea, but it's pretty well established now. --Josh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.3.3 regression test on SCO 5.0.7
Hi All, I have been working through upgrades on legacy business systems on old Linux and SCO plateforms. With the help of this list the Linux problems are handled, thanks. The SCO build is not yet done. I have managed to compile 8.3.3 with the help of the archives. But there were lots of warnings and I would like to see the results of the regression tests. I'm looking for help to make the regression tests happen. Thanks Allan make check at the top level fails with: Running in noclean mode. Mistakes will not be cleaned up. could not determine encoding for locale C_C.C: codeset is initdb: could not find suitable encoding for locale C_C.C Rerun initdb with the -E option. Try initdb --help for more information. The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C_C.C. Moving to .../postgresql-8.3.3/src/test/regress and running ./pg_regress --temp-install=./tmp_check --top-builddir=../../.. --srcdir=/upgrades/postgres/postgresql-8.3.3/src/test/regress --temp-port=55432 --schedule=./parallel_schedule --multibyte=SQL_ASCII --load-language=plpgsql --no-locale ( note the added --no-locale ) gets me further, but fails with: Running in noclean mode. Mistakes will not be cleaned up. The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. The default database encoding has accordingly been set to SQL_ASCII. The default text search configuration will be set to english. creating directory /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_che ck/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 32MB/204800 creating configuration files ... ok creating template1 database in /upgrades/postgres/postgresql-8.3.3/src/test/regre ss/./tmp_check/data/base/1 ... =: is not an identifier child process exited with exit code 1 initdb: data directory /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tm p_check/data not removed at user's request The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgsql-advocacy] [pdxpug] Pg booth staffing at OSCON
On Tuesday 15 July 2008 10:42:07 Daniel Johnson wrote:
Well, you can obviously get into OSCAMP and FOSSCoach and the BOFs and
the other free events. Not that I'd be promoting such a thing (as an
OSCON committee member), but if there's *a* specific session you want to
attend, you can probably persuade one of the several PostgreSQL speakers
to loan you their badge.
No offense, but in the years that I have been going to OSCON I've
never had anywhere near enough money to buy a real pass to OSCON.
Every year it has been volunteering for a booth, or attending OSCAMP.
Last year I was able to afford Ubuntu Live through one of the more
extreme discount codes, but that is it. There are lots of people who
participate as much as they can by way of volunteering, and I am one
of them. We help make the conference happen so please treat us with
respect even if we can't afford to pay are way in.
I can't imagine how you could have taken Josh's post to be anything but
courteous and respectful, but I do encourage you to join us at the BOF where
we can settle it once and for all sumo suits anyone?
http://www.maineventweb.com/page/page/2916926.htm
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Out of memry with large result set
On Mon, Jul 14, 2008 at 11:53 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Try copy (query) to stdout. For me, psql sits at 4.9mb ram on a 3x10^16 row query. klint. Thanks Klint. Can I use a 'copy to' for a query ? I thought I can only do 'copy table to stdout' I will do some tests tomorrow and keep you inform. In 8.2.9 this works: copy (select * from mytable) to stdout; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to found a variable is in a aggregation or not?
yes
It's better obviously,
thanks:D
Yi
On Tue, 2008-07-15 at 15:46 +0200, Pavel Stehule wrote:
so this code is little bit ugly
you can write faster code
create or replace function anytest(val text)
returns boolean as $$
begin
return val in ('hello', 'world','test');
end;
$$ language plpgsql immutable strict;
Pavel
2008/7/15 Yi Zhao [EMAIL PROTECTED]:
I want to check a variable is in a aggregattion or not, so I create a
function as below:
create or replace function anytest(val text) returns boolean as $$
begin
perform 1 where quote_literal(val) in ('hello', 'world', 'test');
if not found then
return false;
else
return true;
end if;
end;
$$ language plpgsql;
but when I used, I got the result below, why?
test=# select anytest('world111');
anytest
-
f
(1 row)
test=# select anytest('world');
anytest
-
f
(1 row)
any help is appreciated.
regards,
Zy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] C-procedure crashed in Postgres 8.3.3 when using 'text' variable (WinXP)
Craig Ringer wrote: That said, PostgreSQL does appear to use its own allocator for memory passed across library boundaries, and if it doesn't pass any FILE pointers across library boundaries either then that's the worst two problems that arise with a C runtime mismatch taken care of. As far as I know, anyway, and I've had the misfortune to have to dig into it a bit. Note that our allocator uses malloc() underneath. And I would think that we do pass FILE pointers around too; AllocateFile works with FILE, so any library that's using that method to get to files would be in trouble (which they should, because it's the way to ensure we don't leak files on transaction abort and also to ensure that the system continues to work on the face of lots of open files). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to create my own result set?
Hi there,
I'm making a really weird stored procedure which makes use of about 5 tables...
and I need to create my own result set based on the computed result of various
FOR loops.
Does anyone knows how can I do that using PL/pgSQL?
I think I need to declare a couple of variables that accept APPENDING but I
don't know if that's possible.
Lets say for example:
CREATE OR REPLACE FUNCTION weirdFunction() RETURNS AS $$
DECLARE
column1 ;
column2 ;
BEGIN
--first row
column1.append('something');
column2.append('something');
--second row
column1.append('again');
column2.append('again');
END;
$$ LANGUAGE plpgsql;
So the result of that should be
something something
again again
Is it possible to do something like that?
Tanks in advance
_
Tenemos lo que búscas…JUEGOS.
http://club.prodigymsn.com/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] C-procedure crashed in Postgres 8.3.3 when using 'text' variable (WinXP)
Alvaro Herrera wrote: Craig Ringer wrote: That said, PostgreSQL does appear to use its own allocator for memory passed across library boundaries, and if it doesn't pass any FILE pointers across library boundaries either then that's the worst two problems that arise with a C runtime mismatch taken care of. As far as I know, anyway, and I've had the misfortune to have to dig into it a bit. Note that our allocator uses malloc() underneath. Sure. That's fine, so long as the wrapper functions palloc and pfree are real functions that reside in the same DLL on windows. You can pass pointers around freely, otherwise you couldn't do much at all ... the problem is with a call path like this: - DLL2 calls a function in DLL1 - DLL1 malloc()'s some memory and returns a pointer to it - DLL2 free()'s the pointer My understanding of the reason that this breaks if DLL1 and DLL2 are linked to different C runtimes is that it's essentially a double free plus a memory leak. The allocator in DLL2 has never allocated the memory so the free call is freeing unallocated memory. It's never properly freed by the allocator in DLL2, so it's leaked as far as that allocator is concerned. It's quite possible that my understanding of the problem isn't correct, though. In any case, the safe thing to do is: - DLL2 calls a function in DLL1 - DLL1 malloc()'s some memory and returns a pointer to it - DLL2 calls a free() wrapper function defined in DLL1 - The free() wrapper in DLL1 calls the real free() to release the memory If palloc() and pfree() are real functions defined in the same DLL, then it should be just fine as use of them will ensure that everything uses the same underlying memory allocator. A problem might arise if Pg ever passes memory not allocated with palloc() around, though. It looks like palloc is a macro for the real function MemoryContextAlloc, and pfree is just a real function - so it should be fine. They don't expose references to malloc() and free() directly. This actually applies to any situation in which multiple malloc()/free() implementations are used - but most people and platforms are sane enough to avoid that. And I would think that we do pass FILE pointers around too; AllocateFile works with FILE, so any library that's using that method to get to files would be in trouble (which they should, because it's the way to ensure we don't leak files on transaction abort and also to ensure that the system continues to work on the face of lots of open files). Yep, I'm pretty sure that'll cause issues when using a mingw-compiled object in a msvc++-compiled copy of Pg. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to create my own result set?
I'll try with what says in the FAQ, but should someone know of a way
to declare a variable that allows appending please tell me
http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions
Hi there,
I'm making a really weird stored procedure which makes use of about 5
tables...
and I need to create my own result set based on the computed result of various
FOR loops.
Does anyone knows how can I do that using PL/pgSQL?
I think I need to declare a couple of variables that accept APPENDING but I
don't know if that's possible.
Lets say for example:
CREATE OR REPLACE FUNCTION weirdFunction() RETURNS AS $$
DECLARE
column1 ;
column2 ;
BEGIN
--first row
column1.append('something');
column2.append('something');
--second row
column1.append('again');
column2.append('again');
END;
$$ LANGUAGE plpgsql;
So the result of that should be
something something
again again
Is it possible to do something like that?
Tanks in advance
_
PlugPlay te trae en exclusiva los mejores conciertos de la red
http://club.prodigymsn.com/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] vacuum taking an unusually long time
Mason Hale [EMAIL PROTECTED] writes: The longest running vacuum has been running more than 6 days at this point. Is it actually *doing* anything, or is it just blocked waiting for someone else? strace or local equivalent would be the most definitive way to check. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.3 regression test on SCO 5.0.7
Harvey, Allan AC [EMAIL PROTECTED] writes: creating template1 database in /upgrades/postgres/postgresql-8.3.3/src/test/regre ss/./tmp_check/data/base/1 ... =: is not an identifier I'd guess you have an incompatible awk or possibly sed. Look at the postgres.bki file generated on this system and compare it to one generated from 8.3 on a non-broken system. It might or might not be easy to determine exactly where the breakage is, but I suspect the short answer is going to be install gawk. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgsql-advocacy] [pdxpug] Pg booth staffing at OSCON
I can't imagine how you could have taken Josh's post to be anything but courteous and respectful, but I do encourage you to join us at the BOF where we can settle it once and for all sumo suits anyone? http://www.maineventweb.com/page/page/2916926.htm I'm going to blame the stress of starting a new job this morning that involves oracle. /me shudders... And now I see that commandprompt has posted a postgres job this afternoon. D'oh! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgsql-advocacy] [pdxpug] Pg booth staffing at OSCON
Daniel Johnson wrote: I can't imagine how you could have taken Josh's post to be anything but courteous and respectful, but I do encourage you to join us at the BOF where we can settle it once and for all sumo suits anyone? http://www.maineventweb.com/page/page/2916926.htm I'm going to blame the stress of starting a new job this morning that involves oracle. /me shudders... And now I see that commandprompt has posted a postgres job this afternoon. D'oh! And we watch these lists :P Joshua D. Drake -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
