[GENERAL] Newby Help needed
Hi all, i'm newby to postgresql: really great product. I'm trying to understand exactly how to set up WAL archiving. I read the doc ( by the way i'm running postgresql-8.1.11 ): i need some explanation on how are created and recycled theWAL bacause i cannot understand, for example, how is filled-up the dir pg_xlog/archive-status. I noticed that sometimes the same file is present in pg_xlog dir and also in archive-status dir with the extension .ready. Then, after some time ( i cannot understand how long, and wehre this parameter, if any, is written ) it disappear from only archive-status directory... Some one can help in understanding these ?? Many thanks in advance to any one will post an answer.. --Andrea -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
pgsql-general@postgresql.org
Hi all, i'm trying to setup PITR on my postgresql ( i run version 1.8.11 );
Following the docs and tips from the list this is what i made:
1) set up a crontab which copys the last-created WAL file in
/home/postgres/WAL
2) set up a shell-script as a the archive_command: it copyes WAL files
from pg_xlog dir in /home/postgres/WAL
3) set up a shell script for doing the backup. It connects to DB and
then issues "SELECT pg_start_backup('full-bck');" , then it execute "tar
--exclude data/pg_xlog -cvzpf archive.tar.gz data" , and finally "SELECT
pg_stop_backup();".
For testing purposes i use another server in which i installed the same
version of PostgreSQL. In it i deleted the DATA dir (
/var/lib/pgsql/data ); then i unpacked the archive.tar.gz ; i created
the pg_xlog dir and, in it, also the archive_status one.
I setup the recovery.conf whit the recovery_command='cp
/home/postgres/WAL/%f "%p"' . I copied all the WAL files fron the
old-server to the new one in /home/postgres/WAL/.
When i try to start the DB it hangsup saying ( in the logs ):
.
LOG: database system was interrupted at 2008-04-11 09:03:48 CEST
LOG: starting archive recovery
LOG: restore_command = "cp /home/postgres/WAL/%f %p"
cp: cannot stat `/home/postgres/WAL/0001.history': No such file or
directory
LOG: restored log file "00010001006A" from archive
LOG: unexpected pageaddr 1/602B2000 in log file 1, segment 106, offset
2826240
LOG: invalid primary checkpoint record
LOG: restored log file "00010001006A" from archive
LOG: unexpected pageaddr 1/6028 in log file 1, segment 106, offset
2621440
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 22672) was terminated by signal 6
LOG: aborting startup due to startup process failure
LOG: logger shutting down
.
Someone can help in understanding where is the problems ???
Thanks in advance,
--Andrea
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
pgsql-general@postgresql.org
Hi all, i'm trying to setup PITR on my postgresql ( i run version 1.8.11 );
Following the docs and tips from the list this is what i made:
1) set up a crontab which copys the last-created WAL file in
/home/postgres/WAL
2) set up a shell-script as a the archive_command: it copyes WAL files
from pg_xlog dir in /home/postgres/WAL
3) set up a shell script for doing the backup. It connects to DB and
then issues "SELECT pg_start_backup('full-bck');" , then it execute "tar
--exclude data/pg_xlog -cvzpf archive.tar.gz data" , and finally "SELECT
pg_stop_backup();".
For testing purposes i use another server in which i installed the same
version of PostgreSQL. In it i deleted the DATA dir (
/var/lib/pgsql/data ); then i unpacked the archive.tar.gz ; i created
the pg_xlog dir and, in it, also the archive_status one.
I setup the recovery.conf whit the recovery_command='cp
/home/postgres/WAL/%f "%p"' . I copied all the WAL files fron the
old-server to the new one in /home/postgres/WAL/.
When i try to start the DB it hangsup saying ( in the logs ):
.
LOG: database system was interrupted at 2008-04-11 09:03:48 CEST
LOG: starting archive recovery
LOG: restore_command = "cp /home/postgres/WAL/%f %p"
cp: cannot stat `/home/postgres/WAL/0001.history': No such file or
directory
LOG: restored log file "00010001006A" from archive
LOG: unexpected pageaddr 1/602B2000 in log file 1, segment 106, offset
2826240
LOG: invalid primary checkpoint record
LOG: restored log file "00010001006A" from archive
LOG: unexpected pageaddr 1/6028 in log file 1, segment 106, offset
2621440
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 22672) was terminated by signal 6
LOG: aborting startup due to startup process failure
LOG: logger shutting down
.
Someone can help in understanding where is the problems ???
Thanks in advance,
--Andrea
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help needed in PITR
Hi all, i'll try a second time to post tips on the problem i'm facing
Fisrt sorry for my impatience; i first try to reed deeply as i can the
docs and then i tryed to setup a test env to run a PITR, but it didn't
work as is expected to.
Now i'm getting confused, so i really need help in trying to understand
what i did wrong, or, more probably, what i didn't understand
Here's what i did:
1) set up a crontab which copys the last-created WAL file in
/home/postgres/WAL
2) set up a shell-script as a the archive_command: it copyes WAL files
from pg_xlog dir in /home/postgres/WAL
3) set up a shell script for doing the backup. It connects to DB and
then issues "SELECT pg_start_backup('full-bck');" , then it execute "tar
--exclude data/pg_xlog -cvzpf archive.tar.gz data" , and finally "SELECT
pg_stop_backup();".
For testing purposes i use another server in which i installed the same
version of PostgreSQL. In it i deleted the DATA dir (
/var/lib/pgsql/data ); then i unpacked the archive.tar.gz ; i created
the pg_xlog dir and, in it, also the archive_status one.
I setup the recovery.conf whit the recovery_command='cp
/home/postgres/WAL/%f "%p"' . I copied all the WAL files fron the
old-server to the new one in /home/postgres/WAL/.
When i try to start the DB it hangsup saying ( in the logs ):
.
LOG: database system was interrupted at 2008-04-11 09:03:48 CEST
LOG: starting archive recovery
LOG: restore_command = "cp /home/postgres/WAL/%f %p"
cp: cannot stat `/home/postgres/WAL/0001.history': No such file or
directory
LOG: restored log file "00010001006A" from archive
LOG: unexpected pageaddr 1/602B2000 in log file 1, segment 106, offset
2826240
LOG: invalid primary checkpoint record
LOG: restored log file "00010001006A" from archive
LOG: unexpected pageaddr 1/6028 in log file 1, segment 106, offset
2621440
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 22672) was terminated by signal 6
LOG: aborting startup due to startup process failure
LOG: logger shutting down
.
Someone can help in understanding where is the problems ???
Thanks in advance,
--Andrea
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: regression in 8.8.2 [was Re: [GENERAL] Very strange error]
On Tue, Feb 06, 2007 at 01:19:28PM -0500, Bruce Momjian wrote: > This is a known bug in 8.2.2 and we are discussing methods of > distributing the fix as quickly as possible. Ok great! Take your time, thanks. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: regression in 8.8.2 [was Re: [GENERAL] Very strange error]
On Tue, Feb 06, 2007 at 03:23:29PM -0300, Alvaro Herrera wrote: > The fix is already in the REL8_2_STABLE branch, so Andrea can certainly > update and confirm if his problem is fixed. Confirmed, after the last cvs checkout it works fine. thanks! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Per-statement trigger in Foreign tables in Posgregsql 9.4 (through Foreign-data wrapper)
Hi everybody, Yesterday I noticed a strange behaviour, I wonder if it is a bug, a non-documented feature or just me. If this is the expected behaviour the documentation should be updated accordingly. This happens on Posgresql 9.4. Consider the following SQL script, where I insert 4 rows into a table using a single insert statement and at the end a per-statement trigger is executed: create table dbpkg.tmp ( user_uuid uuid, role_uuid uuid ); create table dbpkg.user_role ( user_uuid uuid, role_uuid uuid ); CREATE FUNCTION dbpkg.dummy_fn() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'DUMMY'; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER insert_trigger AFTER INSERT ON dbpkg.user_role FOR STATEMENT EXECUTE PROCEDURE dbpkg.dummy_fn(); -- put some values into the temporary table tmp insert into dbpkg.tmp(user_uuid, role_uuid) values (uuid_generate_v4(), uuid_generate_v4()), (uuid_generate_v4(), uuid_generate_v4()), (uuid_generate_v4(), uuid_generate_v4()), (uuid_generate_v4(), uuid_generate_v4()); -- insert all the values from tmp into user_role insert into dbpkg.user_role(user_uuid, role_uuid) select user_uuid, role_uuid from dbpkg.tmp; drop trigger insert_trigger on dbpkg.user_role; drop function dbpkg.dummy_fn(); drop table dbpkg.user_role; drop table dbpkg.tmp; When I execute this locally (i.e. without any foreign table) everything works great. The per-statement trigger dbpkg.dummy_fn is executed only once. If otherwise I do the same using a FDW (i.e the dbpkg.user_role table is declared in a second DB using CREATE FOREIGN TABLE ... SERVER ... OPTIONS ...), the per-statement trigger is executed 4 times, once for every row inserted. I don't know the FDW internals, but it looks like the insert select statement in this case generates more than one insert. Can someone please shed some light on this? Thanks in advance Andrea
[GENERAL] CASE Statement - Order of expression processing
I observed the following behaviour (I tested the following statements in 9.0.4, 9.0.5 and 9.3beta1): $ psql template1 template1=# SELECT CASE WHEN 0=0 THEN 0 ELSE 1/0 END; case -- 0 (1 row) template1=# SELECT CASE WHEN 1=0 THEN 0 ELSE 1/0 END; ERROR: division by zero In this case the CASE behaves as expected. But in the following expression: template1=# SELECT CASE WHEN (SELECT 0)=0 THEN 0 ELSE 1/0 END; ERROR: division by zero (Just to be sure, a "SELECT (SELECT 0)=0;" returns true) It seems that when the "CASE WHEN expression" is a query, the evaluation order changes. According to the documentation, this behaviour is wrong. http://www.postgresql.org/docs/9.0/static/sql-expressions.html (4.2.13. Expression Evaluation Rules): "When it is essential to force evaluation order, a CASE construct (see Section 9.16) can be used. " http://www.postgresql.org/docs/9.0/static/functions-conditional.html(9.16.1. CASE): "If the condition's result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed." "A CASE expression does not evaluate any subexpressions that are not needed to determine the result." Did I miss anything? Or is this really a bug? Thanks, Andrea Lombardoni
Re: [GENERAL] CASE Statement - Order of expression processing
On Mon, Jun 17, 2013 at 11:11 PM, Stefan Drees wrote: > > pg924=# SELECT CASE WHEN 1 != 1 THEN 1/0 ELSE ((SELECT 1)=1)::integer END; > case > -- > 1 > (1 row) > > here the 1/0 is happily ignored. > It gets even stranger: template1=# SELECT CASE WHEN (SELECT 0)=0 THEN 0 ELSE 1/(select 0) END; case -- 0 (1 row) Here it seems that the ELSE does not get evaluated (which is correct). Bye
[GENERAL] Adding "quota user limit" using triggers
Hi, Is possible the limit the size of an user (quota user) as space allocated in a tablespace ? I don't find this option in the 9.0 version of PG, so I think it is not available. Perhaps it should be possible to simulate it using triggers. However before start to work on it , I like to have a confirm and if there is some documentation or information on this problem available. Thx, -- - Andrea Peri . . . . . . . . . qwerty àèìòù -
[GENERAL] Adding "quota user limit" using triggers
>on 06/08/11 11:14 PM, Andrea Peri wrote: >Hi, > > >Is possible the limit the size of an user (quota user) as space allocated in a tablespace ? >I don't find this option in the 9.0 version of PG, >so I think it is not available. > >Perhaps it should be possible to simulate it using triggers. > > >However before start to work on it , I like to have a confirm and if there is some documentation or information on this >problem available. >what exactly would this limit? > > >The total size of tables created by that user regardless of what role inserted data into said tables? There's really no way to >track the data written by a user into tables that multiple users have insert/update privs to. and does it include older tuple >versions that aren't yet vacuumed? calculating pg_total_relation_size is fairly expensive, too, it requires scanning the table >and ancilliary items (indexes, toast storage) to sum up the number of blocks allocated. > I guess the quota limit should be applied to the owner of the table is also the owner of the indexes, sequences, and so on.. regardless of which has inserted on it. And also regardless of vacuumed space. >calculating pg_total_relation_size is fairly expensive, too, it requires >scanning the table >and ancilliary items (indexes, toast >storage) to sum up the number of blocks allocated. yes I think this is a fairly expensive task, But is for me necessary. -- - Andrea Peri . . . . . . . . . qwerty àèìòù -
[GENERAL] Problem with temporary tables
Hello. I am trying to use temporary tables inside a stored procedure, but I get a rather puzzling error. I am currently using PostgreSQL 8.2.7 and this is my stored procedure: CREATE OR REPLACE FUNCTION test() RETURNS bigint AS $$ DECLARE v_oid bigint; BEGIN -- create tmp-table used to map old-id to new-id CREATE TEMPORARY TABLE idmap (oldid bigint PRIMARY KEY, type bigint, newid bigint) ON COMMIT DROP; SELECT INTO v_oid oid FROM pg_class WHERE relname = 'idmap'; RAISE NOTICE 'OOID of idmap %', v_oid; INSERT INTO idmap (oldid, type, newid) VALUES(1, 1, 1); RETURN 1; END; $$ LANGUAGE plpgsql; The first time I invoke the stored procedure, everything goes fine: # begin;select test();commit; BEGIN NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "idmap_pkey" for table "idmap" CONTEXT: SQL statement "CREATE TEMPORARY TABLE idmap (oldid bigint PRIMARY KEY, type bigint, newid bigint) ON COMMIT DROP" PL/pgSQL function "test" line 9 at SQL statement NOTICE: OOID of idmap 475391180 test -- 1 (1 row) COMMIT The second time I invoke the stored procedure, I get an error: # begin;select test();commit; BEGIN NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "idmap_pkey" for table "idmap" CONTEXT: SQL statement "CREATE TEMPORARY TABLE idmap (oldid bigint PRIMARY KEY, type bigint, newid bigint) ON COMMIT DROP" PL/pgSQL function "test" line 9 at SQL statement NOTICE: OOID of idmap 475391184 ERROR: relation with OID 475391180 does not exist CONTEXT: SQL statement "INSERT INTO idmap (oldid, type, newid) VALUES(1, 1, 1)" PL/pgSQL function "test" line 16 at SQL statement ROLLBACK The strange part is that the second time, the OID of the idmap is the same as the one in the first invocation! Am I doing something wrong or is this a bug? Thanks! -- 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] Problem with temporary tables
>> Am I doing something wrong or is this a bug? > > The plan is cached, to avoid this problem, use dynamic SQL. In your > case: > > EXECUTE 'CREATE TEMPORARY TABLE idmap ...' Nice idea, but the problem persists, see log below. I am beginning to mentally place this into the 'bug' area :) CREATE OR REPLACE FUNCTION test() RETURNS bigint AS $$ DECLARE v_oid bigint; BEGIN -- create tmp-table used to map old-id to new-id -- type: 1=skill 3=function EXECUTE 'CREATE TEMPORARY TABLE idmap (oldid bigint PRIMARY KEY, type bigint, newid bigint) ON COMMIT DROP'; SELECT INTO v_oid oid FROM pg_class WHERE relname = 'idmap'; RAISE NOTICE 'OOID of idmap %', v_oid; -- add id mapping (type=1) INSERT INTO idmap (oldid, type, newid) VALUES(1, 1, 1); RETURN 1; END; $$ LANGUAGE plpgsql; # begin;select test();commit; BEGIN NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "idmap_pkey" for table "idmap" CONTEXT: SQL statement "CREATE TEMPORARY TABLE idmap (oldid bigint PRIMARY KEY, type bigint, newid bigint) ON COMMIT DROP" PL/pgSQL function "test" line 9 at execute statement NOTICE: OOID of idmap 475391188 test -- 1 (1 row) COMMIT # begin;select test();commit; BEGIN NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "idmap_pkey" for table "idmap" CONTEXT: SQL statement "CREATE TEMPORARY TABLE idmap (oldid bigint PRIMARY KEY, type bigint, newid bigint) ON COMMIT DROP" PL/pgSQL function "test" line 9 at execute statement NOTICE: OOID of idmap 475391192 ERROR: relation with OID 475391188 does not exist CONTEXT: SQL statement "INSERT INTO idmap (oldid, type, newid) VALUES(1, 1, 1)" PL/pgSQL function "test" line 16 at SQL statement ROLLBACK -- 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] Problem with temporary tables
> You need to use EXECUTE for the INSERT statement as well per error: > > CONTEXT: SQL statement "INSERT INTO idmap (oldid, type, newid) VALUES(1, > 1, 1)" PL/pgSQL function "test" line 16 at SQL statement Thanks, this works and solves my problem. Still, I find this behaviour to be rather quirky. Ideally the generated query plan should notice such cases and either report an error or use the version of the temporary table currently 'live'. At least this quirk should be highlighted both in the plpgsql documentation page http://www.postgresql.org/docs/8.2/interactive/plpgsql-overview.html and linked from the CREATE TABLE page http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html I will propose these changes in the appropriate mailing lists. Have a nice day! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Pgpool-II and Slony-I : query lag, sequences and stored procedures need help
Hello there!
I am currently evaluating the best solution for a high-availability,
load balanced cluster.
Currently I am testing this environment : postgres 8.3.1, pgpool-II
2.1, Slony-I 1.2.14.
I've setup a master server to replicate all tables with Slony-I to a
slave, and a frontend with pgpool
that load-balances the queries (load_balance=true,
replication_mode=false, parallel_query=false,
enable_query_cache=false).
There is a problem : a stored procedure that returns a sequence value
(a global unique ID, used as primary key in INSERTs)
returns the same values in successive calls, under certain conditions.
This is clearly due to the replication lag led by Slony-I.
I would call it a race condition.
Now the question : is there a way to force pgpool to redirect all
queries that involves a specific sequence or a stored procedure
to a specific backend? I tried to setup query partitioning, but only
tables are supported so far.
I know that all queries belonging to a single transaction should be
redirected to the same backend, but I would like to
find out a solution working on the backend, avoiding to check a huge
amount of code that works. ;)
I also know that using the pgpool replication mode will solve the
issue, but it can lead to downtime when adding new backends,
since a synch operation is required. Slony-I implement replication in
a more suitable way from this point of view.
I do not use autoincrement or serial primary key because the
application AS IS doesn't use it.
For the sake of clarity I write down the stored procedure and the
sequence instantiation code:
-- begin code excerpt
CREATE SEQUENCE numgen
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 165024182
CACHE 1;
ALTER TABLE numgen OWNER TO root;
CREATE OR REPLACE FUNCTION pr_next_id(OUT next_number character varying)
RETURNS character varying AS
$BODY$
declare myyear char(4);
declare ii integer;
declare tmp1 varchar(10);
declare tmp2 char(10);
BEGIN
MYYEAR = CAST(EXTRACT(YEAR FROM LOCALTIMESTAMP) AS CHAR(4));
select CAST(nextval('NUMGEN') AS varchar(10)) into TMP1;
TMP2 = '00';
II = length(TMP1);
NEXT_NUMBER = SSUBSTR(MYYEAR,3,4) || SSUBSTR(TMP2,1,10 - II) ||
TMP1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION pr_next_id() OWNER TO root;
-- end code excerpt
The pr_next_id returns the following values when called on the top of
pgpool repeatedly (WRONG RESULT):
080165024184
080165024185
080165024184 [DUPLICATED VALUE]
080165024185 [DUPLICATED VALUE]
080165024186
080165024187
080165024188
If I call the pr_next_id repeatedly directly on the postgres engine, I
get the following (CORRECT RESULT):
080165024112
080165024113
080165024114
080165024115
080165024116
080165024117
080165024118
080165024119
080165024120
Even this post could be off topic here, I think that some people could
have same needs as mine. I already posted in the pgpool mailing
list, but so far no answer.
Thanks in advance!
Regards,
Andrea Moretto
Andrea Moretto
[EMAIL PROTECTED]
---
CONFIDENTIALITY NOTICE
This message and its attachments are addressed solely to the persons
above and may contain confidential information. If you have received
the message in error, be informed that any use of the content hereof
is prohibited. Please return it immediately to the sender and delete
the message.
---
--
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 planner issue
Hi there, I am currently running Postgres 8.3.1. I've got a table called DETAILS, the primary key is : DE_ID char(12), there is another field CO_ID char (12). DE_ID and CO_ID are indexed with a btree. This table is about 140 millions of records. If I execute an "explain select * from details where co_id = '010076015372';" it uses the index. Here follows the plan: "Index Scan using idx_co_id on details (cost=0.00..34.37 rows=2 width=741)" " Index Cond: ((co_id)::bpchar = '010076015372'::bpchar)" If I run "explain analyze select * from details where co_id || co_id = '0100760153722324445';" it runs a sequential scan not using the index (which takes about 100 times than using the index): Seq Scan on details (cost=0.00..8755139.52 rows=819131 width=741) Filter: (((co_id)::text || (co_id)::text) = '010076015372010076015372'::text) Same thing if I try to trick it using a view or a stored procedure. Query planner is not easy to trick! My question is : is there a way to use the index or isn't it possible? Thanks in advance. Andrea Moretto Andrea Moretto [EMAIL PROTECTED] --- CONFIDENTIALITY NOTICE This message and its attachments are addressed solely to the persons above and may contain confidential information. If you have received the message in error, be informed that any use of the content hereof is prohibited. Please return it immediately to the sender and delete the message. --- -- 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] Query planner issue
Dear Richard, you are right. So Postgres takes a lot of time to get out an impossible result! The query I wrote is just a test, the concatenation is only a dummy. On 19/set/08, at 12:33, Richard Huxton wrote: Andrea Moretto wrote: I've got a table called DETAILS, the primary key is : DE_ID char(12), there is another field CO_ID char (12). If I run "explain analyze select * from details where co_id || co_id = '0100760153722324445';" it runs a sequential scan not using the index (which takes about 100 times than using the index): That query can never return any rows. You're appending co_id to itself then testing against a non-repeating string. Furthermore it's 19 chars long rather than 24. Is this really what you're trying to do? Read up on "indexes on expressions" in chapter 11 of the manuals, for indexing this sort of thing. I don't think it will help you here though. -- Richard Huxton Archonet Ltd Andrea Moretto [EMAIL PROTECTED] --- CONFIDENTIALITY NOTICE This message and its attachments are addressed solely to the persons above and may contain confidential information. If you have received the message in error, be informed that any use of the content hereof is prohibited. Please return it immediately to the sender and delete the message. --- -- 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 9.1.1 - availability of xslt_process()
Hi, I'm using the compiled version of PG 9.1.1 on win32 . try-ing to call the xslt_process(text,text) (two parameter version) PG say me it is not available. In the docs are say that it is available only if compiled with the libxslt lib. I see that dll is available in the lib folder. So I guess it should be available. To verify if it is available I search that function in the list of functions with pg-admin. But it is not available.Neither the three parameter version of xslt_process() is available. Perhaps is need to run some script to have it available ? Thx, -- - Andrea Peri . . . . . . . . . qwerty àèìòù -
regression in 8.8.2 [was Re: [GENERAL] Very strange error]
On Tue, Feb 06, 2007 at 10:09:16AM -0500, Michael Slattery wrote: > When does this error crop up? What is the query? Does this select > involve more than one table, or does it involve any homemade > functions? Or overriden functions? My application broke in a big way with the security update to 8.2.2 so I hope this is a bug in 8.2.2 and not an intentional breakage of backwards compatibility in a security update ;). Actually I'm using the REL8_2_STABLE branch in CVS which may be a bit more advanced than the plain 8.2.2, but still it's supposedly a stable branch. The easiest way for me to reproduce is this: cpushare=> create table x (x NUMERIC(28,2) CHECK(x >= 0)); CREATE TABLE cpushare=> insert into x values (0); INSERT 0 1 cpushare=> update x set x = 0; ERROR: attribute 1 has wrong type DETAIL: Table has type numeric, but query expects numeric. cpushare=> Comments welcome. Thanks! ---(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] Lock record
Hi people. I'm writing a client application in Visual Basic, and I need to lock certain records (a read lock) for a long period of time (well, from the start just to the stop of my application) so that no one can modify them. I've seen a lock command, but it seem only capable to lock an entire table. I'm using ADO, and it seem possible to lock a record by opening a recordset on it (with a proper query) and keeping that recordset open (I think that it's the cursor that keeps the lock on the db). Anyone knows a different/better method? Thanks Andrea
[GENERAL] count & distinct
Hi people. I would like to make a query that tells me how many distinct values there are in a column. Standard count doesn't seems to support a count distinct option. select distint count(*) of course doens't work (distinti clause is applied after the result are calculated). I've tried to define a view, but it didn't worked ( create view distValues as select distinct ... but views doesn't support distinct clause)... Well, maybe I should create a new aggregate function, but before spending time on PGSQL guide I would like to know if someone can give me a fast tip... ;-) Thanks in advance Andrea PS: well, of course I can open a cursor on a "select distinct column from table" and then loop over the cursor couting how many values there are, but it seem a bit ugly...
[GENERAL] PostgreSQL on NT: new cygwin?
Hi people, I'm trying to install pgsql on NT using cygwin's last release... well, it seems quite different from b20, you already got cygwin-ipc and there's no more a cygwin-b20 directory, the root directory seems to be unix like even if you don't install Andy Piper tools... is there anyone who has experience on that kind of installation? I've tried to download http://www.s34.co.jp/~luster/pgsql/cygwin32_ipc-1.03-bin-patched.tar.bz2 but bzip2 keeps telling me that this package is corrupted. Anyone has some experience? Bye Andrea
[GENERAL] NT + cygipc + postgresql = boom!
Well, I'm here once again... I've managed to install pgsql 7.02 on my NT box NT 4.0, SP6) with the latest cygwin's release (1.1) and I am using cypipc 1.05... I keep getting "error semaphore semaphore not equal 0" each time I make a query, and sometimes the backend freezes... Joost was right, it seems to be cygipc fault, the above error message is raised from sem.c in cygipc sources... Is there anyone who knows how to solve this problem? Andrea
[GENERAL] make[2]: *** [psql] Error 1
when I compile postgres 6.3.2 under linux I have this error with "make all" and "make install": from make install: /usr/lib/libreadline.a(terminal.o): In function `_rl_enable_meta_key': terminal.o(.text+0x7ce): undefined reference to `tputs' /usr/lib/libreadline.a(terminal.o): In function `_rl_control_keypad': terminal.o(.text+0x7fb): undefined reference to `tputs' terminal.o(.text+0x822): undefined reference to `tputs' make[2]: *** [psql] Error 1 from make all: >. >. >. terminal.o(.text+0x26b): undefined reference to `tgetent' terminal.o(.text+0x35c): undefined reference to `PC' terminal.o(.text+0x366): undefined reference to `BC' terminal.o(.text+0x370): undefined reference to `UP' terminal.o(.text+0x3be): undefined reference to `tgetflag' terminal.o(.text+0x3cf): undefined reference to `tgetflag' terminal.o(.text+0x41f): undefined reference to `tgetflag' terminal.o(.text+0x430): undefined reference to `tgetflag' /usr/lib/libreadline.a(terminal.o): In function `_rl_backspace': terminal.o(.text+0x666): undefined reference to `tputs' /usr/lib/libreadline.a(terminal.o): In function `ding': terminal.o(.text+0x72f): undefined reference to `tputs' /usr/lib/libreadline.a(terminal.o): In function `_rl_enable_meta_key': terminal.o(.text+0x7ce): undefined reference to `tputs' /usr/lib/libreadline.a(terminal.o): In function `_rl_control_keypad': terminal.o(.text+0x7fb): undefined reference to `tputs' terminal.o(.text+0x822): undefined reference to `tputs' make[2]: *** [psql] Error 1 make[2]: Leaving directory `/usr/src/pgsql/src/bin/psql' so, when initdb run there is: initdb: using /usr/local/pgsql/lib/local1_template1.bki.source as input to create the template database. initdb: using /usr/local/pgsql/lib/global1.bki.source as input to create the global classes. initdb: using /usr/local/pgsql/lib/pg_hba.conf.sample as the host-based authentication control file. We are initializing the database system with username postgres (uid=26). This user will own all the files and must also own the server process. initdb: creating template database in /usr/local/pgsql/data/base/template1 Running: postgres -boot -C -F -D/usr/local/pgsql/data -Q template1 WARN:heap_modifytuple: repl is \ 0 initdb: could not create template database initdb: cleaning up by wiping out /usr/local/pgsql/data/base/template1 HELP!
Re: [GENERAL] make[2]: *** [psql] Error 1
>|/usr/lib/libreadline.a(terminal.o): In function `_rl_enable_meta_key': >|terminal.o(.text+0x7ce): undefined reference to `tputs' >|/usr/lib/libreadline.a(terminal.o): In function `_rl_control_keypad': >|terminal.o(.text+0x7fb): undefined reference to `tputs' >|terminal.o(.text+0x822): undefined reference to `tputs' >|make[2]: *** [psql] Error 1 > > > Sounds like the linker is having trouble finding the ncurses library. I am > not sure exactly where, but somewhere in the make file you need to add: > > -l ncurses > > This is a compiler option that will tell the linker to link in the ncurses > routines. > > Hope this helps...james > Ok, now the question is: where? I add it in Makefile.global, but nothig. >From Makefile.global: #- # See the subdirectory template for default settings for these #- CC= gcc YFLAGS= -y -d YACC= /usr/bin/bison LEX= flex AROPT= crs CFLAGS= -I$(SRCDIR)/include -I$(SRCDIR)/backend -lncurses CFLAGS_SL= -fpic ^^^ LDFLAGS= -ldl -lm -lbsd -lreadline -lhistory DLSUFFIX= .so
Re: [GENERAL] make[2]: *** [psql] Error 1
On 13-Oct-98 Marc G. Fournier wrote: >> > This is a compiler option that will tell the linker to link in the ncurses >> > routines. >> > >> > Hope this helps...james >> > >> >> >> >> >> Ok, now the question is: where? >> >> I add it in Makefile.global, but nothig. > > ave you patched in all the v6.3.2 related patches before all this? > I thought we had fixed it post-release... > Ok, now I link ncurses exactly in Makefile.global. It's o right.
[GENERAL] Need the binary of PG9.0 for Windows 32bit
Hi, I need to download the build of Postgres 9.0 compiled for win32bit. I see there is available the 9.0.1 version. But I'm searching specifically the 9.0 for a test. There some link with old binaries ? Thx, Andrea Peri. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [PHP] unsubscribe
unsubscribe LIBIA ANDREA RAMOS SÁNCHEZ INGENIERO DE SISTEMAS ESCUELA COLOMBIANA DE INGENIERIA CEL. 3103271242 OFC. 5452799 - 2550469 CMN-Consulting _ Charla con tus amigos en línea mediante MSN Messenger: http://messenger.latam.msn.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
