Re: [SQL] lock row in table
Hi,
No, I am sure that I can get this message from Delphi. Check it on web site
www.microolap.com
For example when I try to put a non-unique value in a primary key I get message from
database that I get conflict with primary key :-)
of course I use raise ... except ... but everything work perfectly... error handling
works correctly :-)
so I am waiting for an answer for my question how check that the rekord is currently
edited,
Greetings,
Daniel
>
>
>*** REPLY SEPARATOR ***
>
>On 2003-08-28 at 17:42 Yudha Setiawan wrote:
>
>Of course I can lock record with syntax
>"Begin;
>select * from table1 where ID=12 for update;
>update table1 set field1="New value" where ID=12;
>commit;"
>but I can not to inform other user that the record is edited?
>
>MAIN TARGET:
>How to get information that current record is edited? Which function can I
>use?
>
>* as long as i know, the only message you could create is just
>* raise notice 'ur_message'; or raise exception 'ur_error_message';
>* and the only message that delphi could read is just raise exception.
>
>* And if you wanna make your own message, perhaps you must using try and
>except
>* and you could give your own message via ShowMessage('ur_message') or
>other
>* Class object on delphi.
>
>
>Please help me, Maybe someone have similar problem?
>Greetings,
>Daniel
>
>Hope it Help, GOD Bless You and Bravo PostgreSQL.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] COUNT
> "Brian" == Brian C Doyle <[EMAIL PROTECTED]> writes: Brian> Hello, You will need to do "SELECT count(attribute) FROM Brian> table;" or SELECT count(table.attribute);" You need to watch this: acspg=# create table tst ( acspg(# a integer acspg(# ); CREATE acspg=# insert into tst values (0); INSERT 333481 1 acspg=# insert into tst values (null); INSERT 333482 1 acspg=# insert into tst values (2); INSERT 333483 1 acspg=# select count(*) from tst; count --- 3 (1 row) acspg=# select count(a) from tst; count --- 2 (1 row) acspg=# select count(1) from tst; count --- 3 (1 row) acspg=# If you use the attribut name, null values won't be counted. -Dan
[SQL] except on nulls?
I had the following weird experience. Hopefuly iy's my ignorance on the matter (but I sort of think this worked before...) On PostgreSQL 7.0.2, I have an database with two tables, holding different versions of the data set (some rows missing in one of the tables). Example is: CREATE TABLE "test1" ( "f1" text, "f2" date, "f3" "_text", "f4" int4, "f5" character varying(16) ); Then table test2 with the same definition. SELECT f1 FROM test2 EXCEPT SELECT f1 from test1; produced empty result, although test2 contained few more rows. Closer examination showed that both tables contained an 'null' row, with all fields null. After removing both rows, the query would return the proper results. Another query, SELECT * from test2 EXCEPT SELECT * from test1; failed too, giving the following error: ERROR: Unable to identify an operator '<>' for types '_text' and '_text' You will have to retype this query using an explicit cast Any idea why this is happening? Daniel
[SQL] Date Format
In MS Access is: SELECT FORMAT([field],'DD/MM/') AS new name; How I can make in pgaccess? Daniel Hentges Automação Fockink In. Eletricas Ltda. Panambi - RS - Brasil
Re: [SQL] now() with microsecond granulity needed
> "Radoslaw" == Radoslaw Stachowiak <[EMAIL PROTECTED]> writes: Radoslaw> *** Tom Lane <[EMAIL PROTECTED]> [Tuesday, Radoslaw> 20.February.2001, 11:57 -0500]: >> > using now() to init TIMESTAMP fields I got resolution of one >> second. How > can I define DEFAULT in column (TIMESTAMP type) >> to get higher > time-resolution (TIMESTAMP supports >> microseconds). >> >> You could make a variant of now() that relies on gettimeofday() >> instead of time(). Note that you probably won't get >> microsecond precision in any case... Radoslaw> Do You mean changing sources and recompiling? So there Radoslaw> is no way of getting more accurate NOW time directly in Radoslaw> SQL ? /DEFAULT clause/ at this moment? the timeofday function seems to work: acspg=# select timeofday(); timeofday - Wed Feb 21 15:56:43.150389 2001 EST (1 row) acspg=#
[SQL] sum(bool)?
> "Olaf" == Olaf Zanger <[EMAIL PROTECTED]> writes: Olaf> hi there i'd like to add up the "true" values of a Olaf> comparison like Olaf> sum(a>b) Olaf> it just doesn't work like this Olaf> any workaround Try using a case statement: select sum(case when a > b then 1 else 0 end) -Dan
[SQL] Help with 'now', now(), timestamp 'now', ...
> "edipoelder" == edipoelder <[EMAIL PROTECTED]> writes:
edipoelder> times. Then I changed the function
and run, at id =
edipoelder> 14. Change again at id = 15.
Where is underlined
edipoelder> (), i tried to put, 'now', timestamp 'now', etc,
edipoelder> and always get the same time. What i'm doing wrong?
edipoelder>
obs.: -> TABLE TEST (ID SERIAL, ANUMBER INTEGER)
-> TABLE TIMES (ID SERIAL, START TIME, END TIME);
PostgreSQL
-> 7.0.2 under Conectiva Linux
now() returns the time at the start of a transaction. Since pl/psql
functions are implicit transactions, now() will not change inside a
function call.
I've used something like the following:
create function bm(integer) returns text as '
declare
cnt alias for $1;
startt text;
endt text;
begin
startt := timeofday();
for i in 1..cnt LOOP
-- insert statement you want to time here
end loop;
endt := timeofday();
return delta_time_msecs(startt,endt);
end;' language 'plpgsql';
create function delta_time_msecs(text,text) returns float8 as '
declare
startt alias for $1;
endtalias for $2;
spaninterval;
daysfloat8;
hours float8;
minutes float8;
msecondsfloat8;
begin
span := endt::timestamp - startt::timestamp;
mseconds := date_part(''milliseconds'',span)::float8;
minutes := date_part(''minutes'',span)::float8;
hours:= date_part(''hours'',span)::float8;
days := date_part(''days'',span)::float8;
return abs(mseconds + minutes*60.0*1000.0 + hours*3600.0*1000.0 +
days*24.0*3600.0*1000.0);
end;' language 'plpgsql';
select bm(1000)::float8/1000.0;
This will give you the average time, averaged over a thousand queries.
-Dan
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[SQL] Recursive select
> "Martin" == Martin Smetak <[EMAIL PROTECTED]> writes: Martin> Hi all! Anyone know if it's possible to make a recursive Martin> select from a table ? My problem: I got a table of "some Martin> categories" which all points to its parrent Martin> one(tree)...shown below. And I want to select all names of Martin> parrent categories of one child, lets say "fast[4]". Now Martin> I'm solving that with many SQL queries like : "SELECT Martin> main_id FROM cat WHERE id=4;"but I would like to Martin> optimize this. Martin> Anyone can help or point me to a way ?? The Openacs project has implemented a couple of different methods for handling tree queries. Check out these two threads: http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=eC&topic_id=11&topic=OpenACS http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=j6&topic_id=12&topic=OpenACS%204%2e0%20Design Regards, Dan Wickstrom ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] "no wait" option for locks
Hi... anyone knows a workaround for Oracles "no wait" option in "SELECT ... FOR UPDATE"? I wrote a programm which runs twice. Both poll the pgsql database all 5 seconds and make an "SELECT ... FOR UPDATE" for the first row found. After some work and commit the next row is processed. The second task waits until the first hast commited, but it should detect the lock and continue work with next row. How can i detect this lock? Oracle returns with "no wait" option an sql error code. Any hints? best regards Daniel Stuewe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] "no wait" in locks
Hi... anyone knows a workaround for Oracle "no wait" Option in "SELECT ... FOR UPDATE"? I wrote a programm which runs twice. Both poll the pgsql database all 5 seconds and make an "SELECT ... FOR UPDATE" for the first row found. After some work and commit the next row is processed. The second task waits until the first hast commited, but it should detect the lock and continue work with next row. How can i detect this lock? Oracle returns with "no wait" option an sql error code. Any hints? best regards Daniel Stuewe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] "no wait" in locks
Hi... anyone knows a workaround for Oracle "no wait" Option in "SELECT ... FOR UPDATE"? I wrote a programm which runs twice. Both poll the pgsql database all 5 seconds and make an "SELECT ... FOR UPDATE" for the first row found. After some work and commit the next row is processed. The second task waits until the first hast commited, but it should detect the lock and continue work with next row. How can i detect this lock? Oracle returns with "no wait" option an sql error code. Any hints? best regard Daniel Stuewe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] fumbling for join syntax
hi- i have two tables which are used to track questions and answers in a trivia game. the structure of the tables is as follows: CREATE TABLE triv_q_r ( id VARCHAR(10) CONSTRAINT triv_q_r_pkey PRIMARY KEY, t_idVARCHAR(10) CONSTRAINT triv_q_r_t_id_fkey NOT NULL REFERENCES triv_r(id) ON DELETE CASCADE, questionVARCHAR(1024) NOT NULL, num INTEGER NOT NULL, c1 VARCHAR(1024) NOT NULL, c2 VARCHAR(1024) NOT NULL, c3 VARCHAR(1024), c4 VARCHAR(1024), c5 VARCHAR(1024), ans INTEGER NOT NULL, exp VARCHAR(1024), tm INTEGER NOT NULL DEFAULT 1 ); CREATE TABLE triv_a_r ( tq_id VARCHAR(10) CONSTRAINT triv_a_r_tq_id_fkey NOT NULL REFERENCES triv_q_r(id) ON DELETE CASCADE, fid_c VARCHAR(10) NOT NULL, login VARCHAR(20) NOT NULL, ans INTEGER NOT NULL DEFAULT 0, stime TIMESTAMP(2) NOT NULL DEFAULT CURRENT_TIMESTAMP, etime TIMESTAMP(2), tm INTEGER NOT NULL DEFAULT 0 ); right now, i'm givng a scoring overview using the following query: select a.login as user, count(a.login) as score, sum(a.tm)/1000 as time from triv_a_r a, triv_q_r b where a.tq_id = b.id and a.ans = b.ans group by a.login order by score desc, time asc; triv=> \i scoring.sql user | score | time --+---+-- jobtester| 3 |9 paul | 2 |6 marcyun | 1 |2 paulie | 1 |2 winstonchurchill | 1 |2 djk121 | 1 |3 this works fine, but it's been requested that the scoring query also include scores for those who answered incorrectly. i thought that i might be able to do this with a left outer join: select triv_a_r.login as user, count(triv_a_r.login) as score, sum(triv_a_r.tm)/1000 as time from triv_a_r LEFT OUTER JOIN triv_q_r ON (triv_a_r.tq_id = triv_q_r.id) where triv_a_r.ans = triv_q_r.ans group by triv_a_r.login order by score desc, time asc; but this gives me exactly the same results as before, because i still need where triv_a_r.ans = triv_q_r.ans to determine if a question was answered correctly. in short, i'd like to have a single query that extracts the following from these two tables: number attempted, number correct, score, total time, login any pointers woudl be greatly appreciated. thanks- dan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Alter table
I think 7.3 has this feature. Daniel ""Rachel.Vaudron"" <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:Pine.LNX.4.33.0211080811160.12232-10@;lazaret.unice.fr... > Hi, > > I wonder if it is possible to remove a field of a table ? > I haven't found anything about this into the reference manual. > Can I do something like that ?: > > ALTER TABLE table > DROP COLUMN column; > > Thanks > Rachel > ** >[EMAIL PROTECTED] > Laboratoire de prehistoire du Lazaret > 33 bis bd Franck Pilatte 06300 Nice > http://rachel.familinux.org > Windows a bug's life > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Inherancing
You have to insert in th centers-table. Because it is inherited from cities, the record is automatically in cities. Search for SELECT * FROM ONLY in the docs too. > create table cities (id int, name varchar, primary key(id) ); > create table centers (state varchar(2)) inherits (cities); > > ant the tuple > insert into cities values (1, 'Lajeado'); Lajeado isn't a center here. You should do : insert into centers You can do then SELECT * FROM cities -> you will get all cities. Or SELECT * FROM centers -> you will get all centers. Or SELECT * FROM ONLY cities -> you will get only cities who aren't centers. Daniel. "Nasair Junior da Silva" <[EMAIL PROTECTED]> schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Hi people, > supposing i have the tables : > > create table cities (id int, name varchar, primary key(id) ); > create table centers (state varchar(2)) inherits (cities); > > ant the tuple > insert into cities values (1, 'Lajeado'); > > How i do if i want to make this city as a center ? > > Thanks, > > xx===xx > || °v° Nasair Junior da Silva || > || /(_)\ Linux User: 246054 || > || ^ ^ [EMAIL PROTECTED]|| > ||CPD - Desenvolvimento || > ||Univates - Centro Universitário|| > xx===xx > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] "function has no parameter $1" - help.
In > 7.3 you can also CREATE OR REPLACE FUNCTION xxx() RETURNS TRIGGER AS' BEGIN END'LANGUAGE plpgsql; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Fw:
Your ipc-daemon-version is to old. Download the newest version first. (1.13) Daniel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Atomicity of UPDATE, interchanging values in unique column
UPDATE statements are not completely atomic in that index entries are updated separately for each row. A query interchanging two values within a column declared UNIQUE will fail due to the attempt of inserting a duplicate temporarily. It seems like Postgres validates constraints on indexes each time the implementation modifies the index, rather than on the logical transaction boundaries. I tried: UPDATE sometable SET unique_col = CASE WHEN unique_col = firstvalue THEN secondvalue ELSE firstvalue END WHERE unique_col = firstvalue OR unique_col = secondvalue And: BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; UPDATE sometable SET unique_col = firstvalue WHERE unique_col = secondvalue; UPDATE sometable SET unique_col = secondvalue WHERE unique_col = firstvalue; COMMIT; And both queries fail. Of course I could prevent this by first updating one of the entries with a dummy value: BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; UPDATE sometable SET unique_col = dummy WHERE unique_col = secondvalue; UPDATE sometable SET unique_col = secondvalue WHERE unique_col = firstvalue; UPDATE sometable SET unique_col = firstvalue WHERE unique_col = dummy; COMMIT; But that's more like in a 3GL language and does not cleanly express what I want. How can I interchange two values in a unique column? Am I missing something really obvious (like a swap statement)? Is there any reason besides performance for not making index accesses fully ACID-compliant? Doesn't MVCC require this anyway? Thanks for your time, Daniel Alvarez <[EMAIL PROTECTED]> -- +++ GMX - Mail, Messaging & more http://www.gmx.net +++ Bitte lächeln! Fotogalerie online mit GMX ohne eigene Homepage! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Atomicity of UPDATE, interchanging values in unique column
> The first is what you want. PostgreSQL needs some work in the > evaluation of unique indexes to properly support it. > > Namely, when it sees a conflict when inserting into the index, it needs > to record the fact, and revisit the conflict at the end of the command. > Lots of work... OK. The long-term goal would then be to get rid of such oddities. But what can I do right now as a user to solve that issue for my application? There must be a better solution than the additional dummy update. Any ideas? Daniel Alvarez Arribas <[EMAIL PROTECTED]> -- +++ GMX - Mail, Messaging & more http://www.gmx.net +++ Bitte lächeln! Fotogalerie online mit GMX ohne eigene Homepage! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] lock row in table
Hallo everybody, I have a problem that I can not to solve in a simple way. SOME INFORMATION: I have a postgresql database version 7.2.2 on Linux platform. I communicate with Postgresql from a Delphi application using microolap drivers and everything works fine. PROBLEM DESCRIPTION: I have to lock one row in table1 for user1. In the same time other users should be able to read this record but when other user for example user2 want to edit this record user2 should get information "The row you try to edit is currently edit" - or sometihing similar. Of course I can lock record with syntax "Begin; select * from table1 where ID=12 for update; update table1 set field1="New value" where ID=12; commit;" but I can not to inform other user that the record is edited? MAIN TARGET: How to get information that current record is edited? Which function can I use? Please help me, Maybe someone have similar problem? Greetings, Daniel
[SQL] Get x from point?
Hi all, I am wondering if we are able to extract the x-coordinate from a point-type attribute with SQL. I have been looking for this sort of functions from documents and websites but it seems the function does not exist. Or, any altnernate to do so? regards, Daniel Lau Hong Kong University of Science and Technology ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Get x from path/polygon?
Hi all, I succeed in extracting x and y value from POINT. Is it also possible to extract x and y value from PATH and POLYGON? I tried to get x and y from PATH using the similar method. However, the machine says the PATH is not an array and does not allow me to do so. Any hints on this? Thanks in advance. regards, Daniel Lau On Mon, 22 Dec 2003, Michael Fuhr wrote: > On Mon, Dec 22, 2003 at 04:54:01PM +0800, Daniel Lau wrote: > > I am wondering if we are able to extract the x-coordinate from a > > point-type attribute with SQL. I have been looking for this sort of > > functions from documents and websites but it seems the function does not > > exist. Or, any altnernate to do so? > > From the bottom of the Geometric Functions and Operators chapter of > the documentation: > > "It is possible to access the two component numbers of a point as > though it were an array with indices 0 and 1. For example, if t.p > is a point column then SELECT p[0] FROM t retrieves the X coordinate > and UPDATE t SET p[1] = ... changes the Y coordinate. In the same way, > a value of type box or lseg may be treated as an array of two point > values." > > http://www.postgresql.org/docs/current/static/functions-geometry.html > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Type conversion from TEXT to DOUBLE PRECISION
Hi all, Thank you for reading this mail. I am trying to do the following: Extract the first half of _aaa and put it in column _bbb Here is the table, named: _table: Varchar[10] | Double Precision _aaa_bbb _ 1234567890 I used two functions to do it: substring() and to_number(). The SQL is like this: UPDATE _table SET _bbb = to_number(substring(_aaa from 1 for 5), '9'); The machine fails me and said ERROR: invalid input syntac for type numeric: " " I guess the machine can not treat TEXT as CHAR[5]. I tries to CAST TEXT as CHAR[5]. It also doesnt allow me to do so. Can anyone give me some hints on this? regards, Daniel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Type conversion from TEXT to DOUBLE PRECISION
On Fri, 9 Jan 2004, Tom Lane wrote:
> Richard Huxton <[EMAIL PROTECTED]> writes:
> > Since your error seems to be complaining about a space, I'd guess you've got
> > other than numeric values in _aaa.
>
> In fact, with a bit of experimentation I see the same error message:
>
> regression=# select to_number('12345', '9');
> to_number
> ---
> 12345
> (1 row)
>
> regression=# select to_number('1234 ', '9');
> to_number
> ---
> 1234
> (1 row)
>
> regression=# select to_number(' 1234', '9');
> to_number
> ---
> 1234
> (1 row)
>
> regression=# select to_number(' ', '9');
> ERROR: invalid input syntax for type numeric: " "
> regression=# select to_number('z', '9');
> ERROR: invalid input syntax for type numeric: " "
> regression=#
>
> The error message's report of the input string seems a tad misleading,
> especially in the last case. (Karel, is this fixable?) But anyway,
> it sure looks like the problem is bad input data.
>
> regards, tom lane
>
Thanks Tom and Richard. Yes, it is the problem of bad input data. I have
4000 rows of data and there are 10 rows containing blank string ('
'). I have to add a Where clause to carry out the SQL:
UPDATE _table
SET _bbb = to_number(substring(_aaa from 1 for 5), '9')
WHERE _aaa <> ' ';
I guess a function checking if a string contains only numbers would be
betteroff. But I find no such functions. Checking that it's not blank
would be the only solution I can think of.
Thanks again.
regards,
Daniel Lau
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Login information in system tables
Hi, I was wondering if it is possible to query the sytem tables to get an overview of user logins in psql. I've searched the documentation but I can't find a system table that stores login information, but I might have overlooked something... With regards, Daniel Doorduin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [PHP] Secure DB Systems - How to
> Keeping the system administrator from seeing the data while making it
> searchable is difficult. To do this you need to encrypt the data on
> the client side using a key the client has (and this key has to be
> protected from loss) and the only searches you can do are equality
> searches using a hash or encrypted value.
You can also perform regex searches.
Here is an example to get you started:
CREATE TABLE crypto (
id SERIAL PRIMARY KEY,
title VARCHAR(50),
crypted_contentBYTEA
);
INSERT INTO crypto VALUES
(1,'test1',encrypt_iv('daniel','fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes'));
INSERT INTO crypto VALUES
(2,'test2',encrypt_iv('test','fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes'));
INSERT INTO crypto VALUES
(3,'test3',encrypt_iv('struck','fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes'));
SELECT *,decrypt_iv(crypted_content,
'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'), 'aes') FROM crypto;
-- equality search
SELECT
*,decrypt_iv(crypted_content,'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes')
FROM crypto WHERE decrypt_iv(crypted_content,
'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'), 'aes')='struck';
-- regex search
SELECT
*,decrypt_iv(crypted_content,'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes')
FROM crypto WHERE encode(decrypt_iv(crypted_content,
'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'), 'aes'),'escape')
~* 'daniel';
"fooz" is the password and "9MlPeZtpuxKo5m4O4+pd4g==" is the IV (initialization
vector) stored in base64 format. I choose base64 because it is more convenient to
create queries with it.
In the real database I do use a different IV for every row, so I do also store the IV
with the row.
In my case I do generate the IV by PHP with /dev/urandom as a random source.
Greetings,
Daniel Struck
--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg
phone: +352-44116105
fax: +352-44116113
web: http://www.retrovirology.lu
e-mail: [EMAIL PROTECTED]
---(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
Re: [SQL] [PHP] Secure DB Systems - How to
> If you decrypt the data on the database, the sysadmin can see it. Hm, you are right. If one does decrypt the data on the database you have to sent the password to postgresql and so a administrator of the database could easily grasb the password. So the only way to go, would be to perform en/decryption on the client side? > If you are willing to take that chance (e.g. if you primary concern is > some third party getting a snapshot of the DB), then you can do lots of > things. I wonder now; if somebody could achieve to get a snapshot of the database, they could also be able to get the log-file of postgresql. So one would also have to make attention that the information like sql statements don't leak that way. Are there other places where this kind of information could leak? Greetings, Daniel Struck -- Retrovirology Laboratory Luxembourg Centre Hospitalier de Luxembourg 4, rue E. Barblé L-1210 Luxembourg phone: +352-44116105 fax: +352-44116113 web: http://www.retrovirology.lu e-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Storing properties in a logical way.
Hello everybody, In a database there is a table with items, and each item can have 0 to n properties. The objective is to store information about items' properties in a mentioned database in a logical and an efficient way. Usually it is easily done by creating a validation table with a list of possible properties and then creating a n-to-n relationship by means of a linking table (hope I use the correct terms here). But after looking closely at the list of a possible properties, i found out that some of them depend on others. For example, if item is a PDF document, it can have an index. But a document can also have an index with links. Logically, a properties like 'index with links' don't belong to the verification table - they look like a kind of a composite field - 'index with links' is not a stand-alone property, but it also implies that an item also has an 'index' property. On the other hand, it is impossible to decouple 'index' from 'with links', because the second part won't have any meaning without the first part. How can such a kind of data be modeled in a logical way? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Storing properties in a logical way.
On Sun, 05 Sep 2004 19:51:44 +0200, Pierre-Frédéric Caillaud <[EMAIL PROTECTED]> wrote: >> But after looking closely at the list of a possible properties, i found >> out that some of them depend on others. For example, if item is a >> PDF document, it can have an index. But a document can also have an >> index with links. Logically, a properties like 'index with links' >> don't belong to the verification table - they look like a kind of >> a composite field - 'index with links' is not a stand-alone property, >> but it also implies that an item also has an 'index' property. >> On the other hand, it is impossible to decouple 'index' from >> 'with links', because the second part won't have any meaning without >> the first part. > >You mean your properties would be better organized as a tree ? >Or is it even more complicated than that ? I never thought about that possibility - it is an interesting idea, and it solves the logical problem (though there is still a need to ensure that if child property is set, that the user won't be able to also set a parent property - which is probably implementable by using triggers). Though I would prefer, if it is possible, something much simpler, because there are only about 10 properties and 2 'composite' properties - it would probably be an overkill to create a tree for such a small table if a simpler solution exists. Daniel. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] predicate locking
Hi Tom Lane and the postgres help state, "AFAIK, no commercial database does predicate locking either" (12.2.2.1. Serializable Isolation versus True Serializability) But MSSQL (SQL Server) does predicate locking. From MSDN "SERIALIZABLE Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction " So does MSSQL (SQL Server) implement predicate locking? Regards, Daniel Roth MCSD.NET Tom Lane wrote: > Florian Weimer <[EMAIL PROTECTED]> writes: > > Is this a bug, or is SQLxx serializability defined in different terms? > > Strictly speaking, we do not guarantee serializability because we do not > do predicate locking. See for example > http://archives.postgresql.org/pgsql-general/2003-01/msg01581.php > > AFAIK, no commercial database does predicate locking either, so we all > fall short of true serializability. The usual solution if you need the > sort of behavior you're talking about is to take a non-sharable write > lock on the table you want to modify, so that only one transaction can > do the COUNT/INSERT at a time. > > regards, tom lane > > ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] predicate locking
Hi Tom Apologies for my previous double post. In "12.2.2.1. Serializable Isolation versus True Serializability" It sates: "To guarantee true mathematical serializability, it is necessary for a database system to enforce predicate locking, which means that a transaction cannot insert or modify a row that would have matched the WHERE condition of a query in another concurrent transaction" Now that is exactly whats happens when you use SERIALIZABLE is MSSQL. So, by the postgres help documentation's definition of predicate locking, MSSQL does predicate locking. All I am trying to do is correct the help documentation - 12.2.2.1 "so far as we are aware no other production DBMS does either." Regards, Daniel Roth On 6/12/05, Tom Lane <[EMAIL PROTECTED]> wrote: > Daniel Roth <[EMAIL PROTECTED]> writes: > > But MSSQL (SQL Server) does predicate locking. > > > Places a range lock on the data set, preventing other users from > > updating or inserting rows into the data set until the transaction is > > complete. This is the most restrictive of the four isolation levels. > > Range locks are a far cry from general predicate locks. > >regards, tom lane > > PS: kindly don't email me privately while posting the same message to > the lists. You think I have time to answer things twice? > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] int to date
Hi, I have a string containing hexa dump of an 4 bytes integer, like '6AF4805C'. Is it possible to convert it somehow to date type in plpgsql (v8.0.0-rc1)? Doc says that date is represented as 4 bytes internaly. How can I get internal representation of a date type field? So I would be able to produce hexa dump of it like above... Daniel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] table listing queries
Hi, I know that questions like this have been asked in the past, but I can find no definitive answer to one particular part of my problem... Namely, in MySQL I can say: "SHOW TABLES FROM 'dbname'" to list tables in a database I'm not currently connected to. I can find no way of doing this in PgSQL. Is there a way, or is postgres not letting me list the tables until I have connected to the database for security reasons? Thanks in advance, D. -- Daniel Silverstone http://www.digital-scurf.org/ PGP mail accepted and encouraged.Key Id: 2BC8 4016 2068 7895 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Multi-row update w. plpgsql function
Given a set of checkbox values that are submitted through an html form, how do you loop through the submitted values to update more than one row in a table? Imagine a table called 'message_table': mid | message | status +-+--- 1 | Text1 | H 2 | Text2 | H 3 | Text3 | H 4 | Text4 | H A web page presents the user with all messages flagged with 'H'. User checks messages 1,3 and 4 and submits form. (i.e. approved=1&approved=3&approved=4) After performing postgreSQL update, rows 1, 3 and 4 would be updated to: mid | message | status +-+--- 1 | Text1 | A 2 | Text2 | H 3 | Text3 | A 4 | Text4 | A I have never written a plpgsql function, but tried: CREATE OR REPLACE FUNCTION update_messages(approved integer) RETURNS integer AS $body$ DECLARE new_status varchar; new_sample record; BEGIN new_status := 'A'; FOR new_sample IN SELECT * FROM message_table WHERE status='H' ORDER BY mid LOOP UPDATE message_table SET status = new_status WHERE mid = approved; END LOOP; RETURN 1; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; I call the function with: SELECT update_messages(); I'm using apache cocoon, which is why you see the variable placeholder: ); Unfortunately, the function only updates the first value submitted (mid 1), and doesn't loop through the other two values submitted. Can someone help this novice from getting ulcers? Thanks for your help! Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Multi-row update w. plpgsql function
Aaron Koning wrote: Owen makes a good point. Check that you are using the [] in the HTML input variable for the checkboxes. Like: 1 2 3 4 Aaron On 12/13/05, Owen Jacobson <[EMAIL PROTECTED]> wr I'm not familiar with Cocoon, but I'd expect that to return only the first of the "approved" values from the HTTP request. If you add logging to the stored function (RAISE NOTICE 'approved: %', approved; near the start of the function, for instance) and tell PostgreSQL to store the logs, you can see what values your function is actually being called with. What you really want to do is begin a transaction, loop over all the values of approved present in the form data and call (the rewritten version of) update_messages for each one, then commit the transaction. -Owen Thank you all, so much, for taking the time to help me out. Especially as a beginner, where coding IS rocket science. On the general board, Aaron mentioned: UPDATE message_table SET status = 'A' WHERE mid IN (1,2,3); which seems very succinct and economical. I'm gonna have a go at parsing the query string using XSLT, substituting the variable for: UPDATE message_table SET status = 'A' WHERE mid IN ($query_values_here); Again, thanks for the help, Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Executing plpgsql scripts using psql, is that possible?
Hi,
I would like to write some administration plpgsql scripts that populate some
tables (dimension tables) and to execute them using psql. I’m not sure that is
possible with psql as it is with Oracle sqlplus or SQL Server MSQuery:
Oracle sqlplus:
---
DECLARE
V_MyObjectID bigint;
BEGIN
V_MyObjectID := RegisterMyObject('a string', 'another string');
AddObjectProperty(V_MyObjectID, 'a string');
AddObjectProperty(V_MyObjectID, 'another string');
END;
SQL Server MSQuery:
---
DECLARE @MyObjectID int
SET @MyObjectID = RegisterMyObject('a string', 'another string')
EXECUTE AddObjectProperty(MyObjectID, 'a string');
EXECUTE AddObjectProperty(MyObjectID, 'another string');
Any idea how I can translate such a script for psql? I mean, without creating
a function that wraps the whole, of course! :-)
Thanks,
Daniel
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Executing plpgsql scripts using psql, is that possible?
> -Message d'origine- > De : [EMAIL PROTECTED] [mailto:pgsql-sql- > [EMAIL PROTECTED] De la part de John DeSoi > Envoyé : lundi 16 janvier 2006 08:51 > À : Daniel CAUNE > Cc : [email protected] > Objet : Re: [SQL] Executing plpgsql scripts using psql, is that possible? > > > On Jan 16, 2006, at 5:35 AM, Daniel CAUNE wrote: > > > I would like to write some administration plpgsql scripts that > > populate some tables (dimension tables) and to execute them using > > psql. I’m not sure that is possible with psql as it is with Oracle > > sqlplus or SQL Server MSQuery: > > > If you want to execute a plpgsql function from a file using psql, > just call it with SELECT. So your file might have: > > create or replace function my_function(params integer) > returns integer as $$ > DECLARE >V_MyObjectID bigint; > BEGIN >V_MyObjectID := RegisterMyObject('a string', 'another string'); >AddObjectProperty(V_MyObjectID, 'a string'); >AddObjectProperty(V_MyObjectID, 'another string'); > > END; > $$ language plpgsql; > > > SELECT my_function(1); > > > and then psql -f script.sql my_db > Yes, but that requires creating a function while I would prefer not having do so, as I said in my previous mail: "I mean, without creating a function that wraps the whole, of course! :-)". Why? Actually this is not a function; this is a script that inserts static data into dimension tables such as Country, Language, etc. I have several scripts responsible for creating the database and all the objects (tables, views, constraints, indexes, user-defined functions, etc.) of my project. I would like to have some other scripts to initialize dimension tables, i.e. inserting static data in those tables. The idea is to automate the whole creation and initialization of a database on a PostgreSQL server; I already have an Ant task that searches for SQL files, orders them, and runs them against the specified database server. The database and all relative objects are set up in one step. So, I completely understand that I can write an SQL script that: 1 - creates a function that wraps SQL code that inserts static data into dimension tables. 2 - executes that function 3 - destroys that function But actually that is a bit weird, isn't it? Thanks, Daniel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Executing plpgsql scripts using psql, is that possible?
> If you want to use plpgsql it will need to be within a function. In your > reply > you mention creating user-defined functions as part of the set up > procedure. > It would not be weird to include the static data function as part of that > procedure and then call it to load the data. I see no reason to destroy > the > function after use. If that is not the route you want to take you may want > to > look at the following for information on using COPY to load data from a > file > into a table- > www.postgresql.org/docs/8.1/interactive/sql-copy.html > Yes, COPY may be an interesting option too. Thanks! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] [HELP] Defining a function as a procedure
Hi, Is there a way to define a function as a procedure, I mean a function that returns nothing. CREATE OR REPLACE FUNCTION foo() AS $$ BEGIN END; $$ LANGUAGE 'plpgsql'; Actually, PostgreSQL complains as a “function result type must be specified”. I can patch my function so that it compiles but that won’t be really nice: CREATE OR REPLACE FUNCTION foo() RETURNS int AS $$ BEGIN RETURN 1; END; $$ LANGUAGE 'plpgsql'; Is there any other prettier way to do that? Thanks, -- Daniel
[SQL] Function with default value?
Hi, Is it possible to define a function with some default values? CREATE OR REPLACE FUNCTION foo(i IN int, j IN int DEFAULT := 1) … Anyway, I didn’t find such a feature described in the PostgreSQL 8.1 documentation, but sometimes, that doesn’t mean that the feature doesn’t exist! J Thanks, Daniel
Re: [SQL] Function with default value?
> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : dimanche 29 janvier 2006 10:48 > À : Daniel CAUNE > Cc : [email protected] > Objet : Re: [SQL] Function with default value? > > Daniel CAUNE <[EMAIL PROTECTED]> writes: > > Is it possible to define a function with some default values? > > CREATE OR REPLACE FUNCTION foo(i IN int, j IN int DEFAULT := 1) > > No. But you can fake many versions of this with a family of functions: > > CREATE OR REPLACE FUNCTION foo(i IN int, j IN int) ... > > CREATE OR REPLACE FUNCTION foo(i IN int) ... return foo(i, 1) ... > > Remember that PG lets you "overload" a function name by using the same > name with different parameter lists. > > regards, tom lane Yes, thanks Tom, for the second time. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] CREATE INDEX with order clause
Hi, I would like to create an index on a table, specifying an order clause for one of the columns. CREATE INDEX IDX_GSLOG_EVENT_PLAYER_EVENT_TIME_DESC ON GSLOG_EVENT(PLAYER_USERNAME, EVENT_NAME, EVENT_DATE_CREATED DESC); which is not a valid, as the order clause DESC is not supported. Such as index would improve performance of query like: SELECT GAME_CLIENT_VERSION FROM GSLOG_EVENT WHERE PLAYER_USERNAME = ? AND EVENT_NAME = ? AND EVENT_DATE_CREATED < ? ORDER BY EVENT_DATE_CREATED DESC LIMIT 1 Actually, I’m not sure that is useful; perhaps PostgreSQL handles pretty well such query using an index such as: CREATE INDEX IDX_GSLOG_EVENT_PLAYER_EVENT_TIME_DESC ON GSLOG_EVENT(PLAYER_USERNAME, EVENT_NAME, EVENT_DATE_CREATED); Any idea? -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418
[SQL] Does PostgreSQL support job?
Hi, I try to find in the documentation whether PostgreSQL supports job, but I miserably failed. Does PostgreSQL support job? If not, what is the mechanism mostly adopted by PostgreSQL administrators for running jobs against PostgreSQL? I was thinking about using cron/plsql/sql-scripts on Linux. Thanks (Tom Lane J) -- Daniel CAUNE
Re: [SQL] Does PostgreSQL support job?
> -Message d'origine- > De : [EMAIL PROTECTED] [mailto:pgsql-sql- > [EMAIL PROTECTED] De la part de Bruce Momjian > Envoyé : mercredi, février 01, 2006 17:57 > À : Daniel Caune > Cc : [email protected] > Objet : Re: [SQL] Does PostgreSQL support job? > > Daniel Caune wrote: > > Hi, > > > > > > > > I try to find in the documentation whether PostgreSQL supports job, but > > I miserably failed. Does PostgreSQL support job? If not, what is the > > mechanism mostly adopted by PostgreSQL administrators for running jobs > > against PostgreSQL? I was thinking about using cron/plsql/sql-scripts > > on Linux. > > The unix cron systems is what most people use. > OK. Thanks. That's fine! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Does PostgreSQL support job?
> -Message d'origine- > De : Owen Jacobson [mailto:[EMAIL PROTECTED] > Envoyé : mercredi, février 01, 2006 18:00 > À : Daniel Caune; [email protected] > Objet : RE: [SQL] Does PostgreSQL support job? > > Daniel Caune wrote: > > Hi, > > > > I try to find in the documentation whether PostgreSQL supports job, > > but I miserably failed. Does PostgreSQL support job? If not, what > > is the mechanism mostly adopted by PostgreSQL administrators for > > running jobs against PostgreSQL? I was thinking about using > > cron/plsql/sql-scripts on Linux. > > The answer really depends on what you mean by "jobs". If you have a > database task that can be expressed as a series of commands with no > interaction involved, you can just put those commands in a file (your-job- > name.sql) and run it using psql and cron: > > # replace leading stars with cron time settings > * * * * * psql your-database -i your-job-name.sql > Yes, that's it. A job is a task, i.e. set of statements, which is scheduled to run against a RDBMS at periodical times. Some RDBMS, such as SQL Server and Oracle, support that feature, even if such a feature is managed differently from a RDBMS to another. OK. I get it. I will use cron and psql as I was planning to do so. > If you need something more complex, either a function which is executed > from a script or a full-blown client program may be required. IME that's > fairly rare. > I'm not sure to understand. Why calling a function from a script is different from executing a series of SQL commands? I mean, I can run a script defined as follows: SELECT myjob(); where myjob is a stored procedure such as: CREATE OR REPLACE FUNCTION myjob() RETURNS void AS $$ END; $$ LANGUAGE PLPGSQL; Does that make sense? -- Daniel CAUNE ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Does PostgreSQL support job?
> -Message d'origine- > De : [EMAIL PROTECTED] [mailto:pgsql-sql- > [EMAIL PROTECTED] De la part de Alvaro Herrera > Envoyé : mercredi 1 février 2006 19:28 > À : Daniel Caune > Cc : Owen Jacobson; [email protected] > Objet : Re: [SQL] Does PostgreSQL support job? > > Daniel Caune wrote: > > > Yes, that's it. A job is a task, i.e. set of statements, which is > > scheduled to run against a RDBMS at periodical times. Some RDBMS, > > such as SQL Server > > ..., the current alpha MySQL, ... > > > and Oracle, support that feature, even if such a > > feature is managed differently from a RDBMS to another. > > > I was amused when I read the MySQL news in LWN.net, because most > comments were things like "what the hell has this half-baked feature has > to do in a RDBMS anyway". > > http://lwn.net/Articles/167895/ > It's true that implementing a job management within an RDBMS is somewhat reinventing the wheel, especially on UNIX systems where cron exists (even on Windows, which supports scheduled tasks). Anyway, job support within a RDBMS sounds more like a facility. "While I have built a number of large and small applications with various time-based event scheduling tables stored in an SQL database, including things like triggers that send asynchronous notifications to daemon clients to advise them to re-query for updated schedules, it never in my wildest imaginings occured to me to actually initiate execution autonomously from the database back end." [zblaxell, 2006-01-25, http://lwn.net/Articles/167895/] Well, perhaps zblaxell has only worked on operational systems (OLTP), but such autonomy is sometimes useful in low-cost business intelligence systems (OLAP). -- Daniel CAUNE ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Does PostgreSQL support job?
> > I'm not sure to understand. Why calling a function from a script is > different from executing a series of SQL commands? I mean, I can run a > script defined as follows: > > > > SELECT myjob(); > > > > where myjob is a stored procedure such as: > > > > CREATE OR REPLACE FUNCTION myjob() > > RETURNS void > > AS $$ > > > > END; > > $$ LANGUAGE PLPGSQL; > > > > Does that make sense? > > It does make sense if myjob() does more than just execute a bunch of > statements, e. G. it contains if(), loops or something else. > > PLPGSQL is turing complete, plain SQL is not. > Yes, indeed, that was the idea! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Does PostgreSQL support job?
> Daniel Caune wrote:
> >>> I'm not sure to understand. Why calling a function from a script
is
> >>> different from executing a series of SQL commands?
>
> [snip]
> >>>Does that make sense?
> >>It does make sense if myjob() does more than just execute a bunch of
> >>statements, e. G. it contains if(), loops or something else.
> >>PLPGSQL is turing complete, plain SQL is not.
> > Yes, indeed, that was the idea!
>
> There's another reason: For updating the cron job SQL commands, you
need
> root access (or at least shell access) to the database machine. For
> updating a stored procedure, you need just the appropriate rights in
the
> database.
>
> On larger deployments, this can be an important difference.
>
You are absolutely right. That is such detail I was thinking over.
Managing stored procedures into a RDBMS seems less laborious than
modifying some SQL scripts on the file system. I mean there is always a
need to define initially a script, run by the cron/psql couple, which
calls a stored procedure responsible for doing the job ("SELECT
myjob();"). Therefore it is easier to modify implementation details of
the job without having to modify the script run by the cron/psql. On
another hand, it seems easier to test modification by patching a stored
procedure directly in the RDBMS and making some tests on-the-fly.
--
Daniel CAUNE
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] How to force PostgreSQL using an index
Hi, Is there a way to force PostgreSQL using an index for a SELECT statement? I just want to confirm that the index PostgreSQL decides to use is better than the index I supposed PostgreSQL would use (I already analyze the table). Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418
Re: [SQL] How to force PostgreSQL using an index
> On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote: > > Hi, > > > > > > > > Is there a way to force PostgreSQL using an index for a SELECT > > statement? I just want to confirm that the index PostgreSQL decides to > > use is better than the index I supposed PostgreSQL would use (I already > > analyze the table). > > Your best bet is to do > > set enable_indexscan=false; > > and then do the EXPLAIN ANALYSE for your select. > > You might also find that fiddling with other settings affects the > planner's idea of what would be a good plan. The planner is > sensitive to what it thinks it knows about your environment. > I see, but that doesn't explain whether it is possible to specify the index to use. It seems that those options just force PostgreSQL using another plan. For example, I have a table that contains historical data from which I try to get a subset for a specified period of time: SELECT FROM GSLOG_EVENT WHERE EVENT_NAME = 'player-status-update' AND EVENT_DATE_CREATED >= AND EVENT_DATE_CREATED < I have an index on EVENT_DATE_CREATED that does it job. But I though that I can help my favourite PostgreSQL if I create a composite index on EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED is more dense that EVENT_NAME). PostgreSQL prefer the simple index rather than the composite index (for I/O consideration, I suppose). I wanted to know how bad the composite index would be if it was used (the estimate cost). Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to force PostgreSQL using an index
> > > > Is there a way to force PostgreSQL using an index for a SELECT > > > > statement? > > > > > > Your best bet is to do > > > > > > set enable_indexscan=false; > > > > > > and then do the EXPLAIN ANALYSE for your select. > > > > I see, but that doesn't explain whether it is possible to specify the > > index to use. It seems that those options just force PostgreSQL using > > another plan. > > (snip) > > > I have an index on EVENT_DATE_CREATED that does it job. But I though > > that I can help my favourite PostgreSQL if I create a > > composite index on > > EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED > > is more dense that EVENT_NAME). > > > > PostgreSQL prefer the simple index rather than the composite index (for > > I/O consideration, I suppose). I wanted to know how bad the composite > > index would be if it was used (the estimate cost). > > Drop the simple index and re-create it when you're done? > Yes, that is a solution! I will try that! :-) > As I understand it, the problem with letting clients specify which indexes > to use is that they tend, on the whole, to be wrong about what's most > efficient, so it's a feature almost specifically designed for shooting > yourself in the foot with. I agree that it'd be useful for experimenting > with indexing schemes, but then, so is DROP INDEX. > Yes, indeed, such a feature could be badly used. However it may happen sometimes that the planner is wrong; I already encountered such situations with both Oracle 9i and SQL Server 2000, even with statistics calculated. That is rare but that happens. Such options /*+ */ or WITH(INDEX(...)) help in such situations, even if that really sucks for the reason you know. Daniel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to force PostgreSQL using an index
> "Owen Jacobson" <[EMAIL PROTECTED]> writes: > > On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote: > >> I see, but that doesn't explain whether it is possible to specify the > >> index to use. It seems that those options just force PostgreSQL using > >> another plan. > > > Drop the simple index and re-create it when you're done? > > BTW, the cute way to do that is > > BEGIN; > DROP INDEX unwanted; > EXPLAIN ANALYZE whatever...; > ROLLBACK; > > No need to actually rebuild the index when you are done. > > This does hold an exclusive lock on the table for the duration of your > experiment, so maybe not such a good idea in a live environment ... but > then again, dropping useful indexes in a live environment isn't a good > idea either, and this at least reduces the duration of the experiment by > a good deal. > Thanks, that's great! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to force PostgreSQL using an index
> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : mercredi, février 15, 2006 17:47 > À : Daniel Caune > Cc : Andrew Sullivan; [email protected] > Objet : Re: [SQL] How to force PostgreSQL using an index > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > SELECT > > FROM GSLOG_EVENT > > WHERE EVENT_NAME = 'player-status-update' > > AND EVENT_DATE_CREATED >= > > AND EVENT_DATE_CREATED < > > > I have an index on EVENT_DATE_CREATED that does it job. But I though > > that I can help my favourite PostgreSQL if I create a composite index on > > EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED > > is more dense that EVENT_NAME). > > Wrong ... should be EVENT_NAME first. Think about the sort order of the > data to see why --- your query represents a contiguous subset of the > index if EVENT_NAME is first, but not if EVENT_DATE_CREATED is first. > > regards, tom lane Yes, you're right! ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Sum If
Hi Guys, I'm new on this group, and I have a question, is there a way to do a "sum if" (kind of)?What i want to do is the following.Select customers.custid, if (sales.itemname = 'candy', sum(sales.count)) as "Sales candies", if (sales.itemname = 'some', sum(sales.count)) as "Sales Some"from ...join ...where .group by customers.custid ...Thanks in advanced, and best regards,Daniel Hernández.Tijuana, BC, México."More you learn, more you earn".Join Excite! - http://www.excite.comThe most personalized portal on the Web!
Re: [SQL] Sum If
Hi Jim, Thanks for the tip, It worked!Thanks a lot!!!Daniel Hernández.Tijuana, BC, México."More you learn, more you earn".try select ...,sum(case when sales.itemname = 'some' then sales.count else 0 end) as "Sales Candies"from your_table_heregroup by ...<[EMAIL PROTECTED]>Join Excite! - http://www.excite.comThe most personalized portal on the Web!
[SQL] Missing fields on Query result.
Hi Guys, I have another question, but in this time is regarding to a query that supose to return son char fields, but they don't show up, I'm using ODBC Driver 7.03.02.00 with Delphi 6.Thanks in advanced, and regards,Daniel Hernández.Tijuana, BC, México."More you learn, more you earn".Join Excite! - http://www.excite.comThe most personalized portal on the Web!
Re: [SQL] Missing fields on Query result.
Never Mind, it's solved now,Thanx any way, regards,Daniel Hernández.Tijuana, BC, México."More you learn, more you earn". --- On Fri 02/24, Daniel Hernandez < [EMAIL PROTECTED] > wrote:From: Daniel Hernandez [mailto: [EMAIL PROTECTED]To: [email protected]: Fri, 24 Feb 2006 13:08:51 -0500 (EST)Subject: [SQL] Missing fields on Query result. Hi Guys, I have another question, but in this time is regarding to a query that supose to return son char fields, but they don't show up, I'm using ODBC Driver 7.03.02.00 with Delphi 6.Thanks in advanced, and regards,Daniel Hernández.Tijuana, BC, México."More you learn, more you earn".Join Excite! - http://www.excite.comThe most personalized portal on the Web!Join Excite! - http://www.excite.comThe most personalized portal on the Web!
[SQL] plsql / time statement
Hi, Is there any option to set so that psql provides the execution time of each SQL statement executed? Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418
[SQL] how to add primary key to existing table with multiple primary keys
Hi there, I am trying to add another primary key to an existing table with two other primary keys. I got the following error when I tried this command: alter table extprobe2tissue ADD primary key (expid); ERROR: multiple primary keys for table "extprobe2tissue" are not allowed The details of the extprobe2tissue table is: \d extprobe2tissue; Table "public.extprobe2tissue" Column | Type | Modifiers -++--- probeid | integer | not null tissueid | integer | not null value | double precision | not null entrydate | date | not null description | character varying(200) | qval | double precision | expid | integer | not null Indexes: "extprobe2tissue_pkey" PRIMARY KEY, btree (probeid, tissueid) "extprobe2tissue_probeid" btree (probeid) "extprobe2tissue_tissueid" btree (tissueid) Foreign-key constraints: "extprobe2tissue_tissueid_fkey" FOREIGN KEY (tissueid) REFERENCES tissue(tissueid) "extprobe2tissue_probeid_fkey" FOREIGN KEY (probeid) REFERENCES extprobe(probeid) Any suggestion would be much appreciated. Thanks!
Re: [SQL] pgsql aggregate: conditional max
> Hi, > > I need a special aggregation function. For instance, given the following > table data: > >aid| cat | weight > --+-+- > a1 | Drama | 1 > a1 | Romance | 6 > a1 | Short | 1 > a1 | Other | 7 > a2 | Comedy | 1 > a2 | Drama | 2 > a3 | Drama | 1 > a3 | Adult | 2 > a3 | Comedy | 1 > a3 | Other | 1 > > I want to group by "aid" and choose the category (i.e., "cat") with the > largest "weight": > > aid | max_weighted_cat > +- > a1 | Other > a2 | Drama > a3 | Adult > > Any ideas? Thank you! :) > SELECT aid, cat FROM table, ( SELECT aid, max(weight) as weight FROM table GROUP BY aid) AS tablemaxweight WHERE table.aid = tablemaxweight.aid AND table.weight = tablemaxweight.aid; There is a limit case you don't specify how to deal with, when two or more categories have the same maximum weight. The query I wrote retrieves all the categories that have the maximum weight, but perhaps you just want one per aid. -- Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Ask a PostgreSql question (about select )
> Objet : Re: [SQL] Ask a PostgreSql question (about select ) > > Please post questions to the list. I'm forwarding this to the SQL > list, as I think it's probably most applicable. I don't know much > about the Oracle syntax you've used. Hopefully someone will be able > to help you. > > On Mar 13, 2006, at 12:30 , min wrote: > > > Please help me one PostgreSQL Statement, Thanks > > > > in Oracle > > select rownum,groupid,qty from abc > > --- --- > > 1 a5 3 > > 2 a2 4 > > 3 a3 5 > > 4 > > 5 > > . > > . > > . > > > > > > in PostgreSql > > > > How to wirte Statement ( Rownum -> change ??) > > PostgreSQL doesn't have the Oracle ROWNUM feature. But if you were using ROWNUM to limit a result set, you are more likely to use the LIMIT feature in PostgreSQL: http://archives.postgresql.org/pgsql-sql/2005-05/msg00127.php -- Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] connectby documentation
Hi, I'm searching for an Oracle START WITH ... CONNECT BY PRIOR ... equivalence. It seems that PostgreSQL (version >= 7.4) supports a function connectby that provides similar feature. Unfortunately I don't find any documentation on that function. Could you please give me a link on such documentation? Note: Just an example of the Oracle START WITH ... CONNECT BY PRIOR ... behaviour. 4| || 2| / \ | 1 3 | Hierarchy dependency order / \ | | 5 | 6 | |/ | 7V SELECT JobId, JobParentId FROM JobDependency START WITH JobParentId IN ( _Root_Datamarts_ ) CONNECT BY PRIOR JobId = JobParentId JOBID JOBPARENTID - --- 2 4 1 2 5 1 7 1 3 2 6 3 7 6 Regards, -- Daniel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] connectby documentation
> -Message d'origine- > De : Michael Fuhr [mailto:[EMAIL PROTECTED] > Envoyé : lundi, mars 13, 2006 11:12 > À : Daniel Caune > Cc : postgresql sql list > Objet : Re: [SQL] connectby documentation > > On Mon, Mar 13, 2006 at 10:37:37AM -0500, Daniel Caune wrote: > > I'm searching for an Oracle START WITH ... CONNECT BY PRIOR ... > > equivalence. It seems that PostgreSQL (version >= 7.4) supports a > > function connectby that provides similar feature. Unfortunately I don't > > find any documentation on that function. Could you please give me a > > link on such documentation? > > connectby() is part of the contrib/tablefunc module. You'll need > to install that module and load it into your database. > > Somebody has made a CONNECT BY patch but the developers have objected > to it for various reasons. Search the list archives for discussion. > That sounds good. I tried to install PostgreSQL contrib modules on my Linux/Debian distribution: > apt-get install postgresql-contrib (...) The following extra packages will be installed: libpq3 libxml2 postgresql postgresql-7.4 postgresql-client postgresql-client-7.4 postgresql-contrib-7.4 (...) 7.4?! Huh... Is there any sources.list a bit more updated? Where can I download PostgreSQL contrib modules. The documentation 8.1 doesn't help so much. Where can I find more documentation on available contrib. modules? Thanks, -- Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] connectby documentation
> "outdated" packets is unfortunately a big issue on Debian. If you want > to have up-to-date apt-packages try > > www.backports.org > > Add one of the mirrors from the list to your sources.list, then run > apt-get update and then try to install again ... > :-) And you'll see, that you can install newer versions than 7.4 :-) > Sorry, this is not my day: "apt-get install postgresql-contrib-8.1" works fine... Just a link on the documentation that fully explains how connectby() works would be great! :-) Thanks, -- Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] connectby documentation
> > Sorry, this is not my day: "apt-get install postgresql-contrib-8.1" > > works fine... Just a link on the documentation that fully explains how > > connectby() works would be great! :-) > > The contrib package should have installed a file named README.tablefunc. > You are right. The documentation is located in /usr/share/doc/postgresql-contrib-8.1/ . Wow, that was the quest for the Holy Grail! :-) -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] connectby documentation
> > > Sorry, this is not my day: "apt-get install postgresql-contrib-8.1" > > > works fine... Just a link on the documentation that fully explains > how > > > connectby() works would be great! :-) > > > > The contrib package should have installed a file named > README.tablefunc. > > > > You are right. The documentation is located in > /usr/share/doc/postgresql-contrib-8.1/ . > > Wow, that was the quest for the Holy Grail! :-) > Huh... It seems that installing the package postgresql-contrib does not make the work itself. I provide hereafter a description about how to install the function connectby (I didn't find such documentation and I don't know where to write this documentation): > apt-get install postgresql-contrib-8.1 > emacs /usr/share/postgresql/8.1/contrib/tablefunc.sql Replace "$libdir" by "/usr/lib/postgresql/8.1/lib/tablefunc.so" (I suggest to modifying only a copy of this file). > su postgres > psql -f /usr/share/postgresql/8.1/contrib/tablefunc.sql You can check that the work is done as follows: > psql => \df connectby List of functions Schema | Name| Result data type | Argument data types +---+--+ - public | connectby | setof record | text, text, text, text, integer public | connectby | setof record | text, text, text, text, integer, text public | connectby | setof record | text, text, text, text, text, integer, text I hope that will help another PostgreSQL newbie. -- Daniel ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] connectby documentation
> -Message d'origine- > De : Michael Fuhr [mailto:[EMAIL PROTECTED] > Envoyé : lundi, mars 13, 2006 19:26 > À : Daniel Caune > Cc : [EMAIL PROTECTED]; postgresql sql list > Objet : Re: [SQL] connectby documentation > > On Mon, Mar 13, 2006 at 06:38:14PM -0500, Daniel Caune wrote: > > I provide hereafter a description about how to install the function > > connectby (I didn't find such documentation and I don't know where to > > write this documentation): > > README.tablefunc contains instructions on how to load the module > into a database. > > > > apt-get install postgresql-contrib-8.1 > > > emacs /usr/share/postgresql/8.1/contrib/tablefunc.sql > > > > Replace "$libdir" by "/usr/lib/postgresql/8.1/lib/tablefunc.so" (I > > suggest to modifying only a copy of this file). > > That shouldn't be necessary unless the package installed the shared > objects somewhere other than where the database was expecting. > What's the output of "pg_config --libdir --version"? > /usr/lib PostgreSQL 8.1.2 > > > su postgres > > > psql -f /usr/share/postgresql/8.1/contrib/tablefunc.sql > > Or, omitting the su, psql -U postgres > > -- > Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] help with function
> Hello, > > I have 2 tables where each table has a column named "comments" and the > tables are related as a one to many. I want to concatenate all the > comments of the many side to the one side so I wrote the following > plpgsql function to do so. > > > CREATE OR REPLACE FUNCTION fixcomments() >RETURNS int4 AS > $BODY$ > DECLARE > mviews RECORD; > i int4; > BEGIN > > FOR mviews IN SELECT * FROM saleorder WHERE comments is not null > and comments <> '' LOOP > > -- Now "mviews" has one record from saleorder > > EXECUTE 'UPDATE sale SET comments = ' || > quote_ident(sale.comments) || quote_ident(mviews.comments) > || ' WHERE sale.id = ' || quote_ident(mviews.sale_id); EXECUTE 'UPDATE sale SET comments = ''' || quote_ident(sale.comments || mviews.comment) || ''' WHERE sale.id = ''' || quote_ident(mviews.sale_id) || ; Does that help? > i := i + 1; > END LOOP; > > RETURN i; > END; > $BODY$ >LANGUAGE 'plpgsql' VOLATILE; > > > > I have the following error when I run this code: > > > ERROR: missing FROM-clause entry for table "sale" > CONTEXT: SQL statement "SELECT 'UPDATE sale SET comments = ' || > quote_ident(sale.comments) || quote_ident( $1 ) || ' WHERE sale.id = ' > || quote_ident( $2 )" > PL/pgSQL function "fixcomments" line 11 at execute statement > > > Doesn anybody know what I am doing wrong here ? > > Lacou. > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend ---(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
[SQL] Power cut and performance problem
Hi, We had a power cut lastly and it seems that our PostgreSQL database suffers from performance since. For example, a simple query such as "SELECT MIN(a-primary-key-column) FROM a-table" takes quite a very long time; actually I gave up before getting the result. I shutdown and started up the database, and I took at the log file; I don't see any fatal error: LOG: database system was interrupted at 2006-03-20 22:20:22 GMT LOG: checkpoint record is at 10C/14919ED4 LOG: redo record is at 10C/1487E270; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 166159120; next OID: 41575 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 10C/1487E270 LOG: incomplete startup packet FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up LOG: record with zero length at 10C/14CF39F0 LOG: redo done at 10C/14CF39B4 LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database "postgres" LOG: incomplete startup packet LOG: received fast shutdown request LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2006-03-20 22:30:09 GMT LOG: checkpoint record is at 10C/14CF3A34 LOG: redo record is at 10C/14CF3A34; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 166159788; next OID: 41575 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database "postgres" LOG: incomplete startup packet LOG: autovacuum: processing database "postgres" LOG: incomplete startup packet LOG: received fast shutdown request LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2006-03-20 22:31:24 GMT LOG: checkpoint record is at 10C/14CF3A78 LOG: redo record is at 10C/14CF3A78; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 166159796; next OID: 41575 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database "postgres" LOG: autovacuum: processing database "postgres" LOG: incomplete startup packet Where can I check, please? Is it more likely a hardware problem (the machine seems ok, no error detected)? Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Power cut and performance problem
> I see you're running autovacuum. What's your disk subsytem look like? By > chance is it sitting on a RAID 5 that's running in degraded mode right now > while it scrubs? > Yes, that should be the problem. I will check that tomorrow morning with a Linux administrator. Thanks. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Update counter when row SELECT'd ... ?
> I have a simple table: > > name, url, counter > > I want to be able to do: > > SELECT * FROM table ORDER BY counter limit 5; > > But, I want counter to be incremented by 1 *if* the row is included in > that 5 ... so that those 5 basically move to the bottom of the list, and > the next 5 come up ... > > I've checked CREATE TRIGGER, and that doesn't work 'ON SELECT' ... is > there anything that I *can* do, other then fire back an UPDATE based on > the records I've received? > > Thanks ... > You mean that you want to write a SELECT statement that returns the 5 first rows that have the smallest counter, and just after what, you would like to increment their counter, right? I was thinking of using a table function, something like (I didn't test it): CREATE OR REPLACE FUNCTION my_first_url(P_Limit IN int) RETURNS SETOF table AS $$ BEGIN FOR V_Record IN SELECT * FROM table ORDER BY counter LIMIT P_Limit LOOP UPDATE table SET counter = counter + 1 WHERE name = V_Record.name /* AND url = V_Record.url */; -- if needed RETURN V_Record; END LOOP; END; $$ LANGUAGE PLPGSQL; -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Power cut and performance problem
> BTW, I didn't complete my first thought above, which was to ask when you > last > vacuumed the DB, but then I saw that you were running autovac, so that > wasn't > likely the problem. > > BTW, if the problem is actually a raid array that is rebuilding, it should > be > (hopefullY) fixed by tomorrow morning. > An administrator is checking the raid status this morning. Anyway, I did some tests and it seems that some results are weird. For example, the execution of the following query is fast as it used to be (gslog_event_id is the primary key on gslog_event): select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms) while the following query is really slow (several minutes): select min(gslog_event_id) from gslog_event; (index on the primary key is taken) I'm not a hardware expert at all, but I supposed that the whole performance would be degraded when a problem occurs with RAID disks. Am I wrong? Could it be something else? Are there some tools that check the state of a PostgreSQL database? -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Power cut and performance problem
> > For example, the execution of the following query is fast as it used to > > be (gslog_event_id is the primary key on gslog_event): > > > > select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms) > > > > > > while the following query is really slow (several minutes): > > > > select min(gslog_event_id) from gslog_event; (index on the primary key > > is taken) > > > > > > I'm not a hardware expert at all, but I supposed that the whole > > performance would be degraded when a problem occurs with RAID disks. Am > > I wrong? Could it be something else? Are there some tools that check > > the state of a PostgreSQL database? > > You would be correct, a hardware problem should manifest itself on both > those > queries. What is the explain analyze output of those two queries? It's > possible you have a corrupt index on gslog_event. If that's the case, a > reindex would likely remedy the problem. Is postgres logging any errors? > The UNIX administrator confirms that this is not a RAID problem. I truncate my table. This is not the most efficient way, but it's okay because this is a data stage table. It seems that it fixes my performance problem. As you said, perhaps the problem was more related to index corruption. Truncating data and inserting new data recreate the index and therefore fix the problem. Thanks, -- Daniel ---(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
[SQL] Custom type
Hi, How can I enter description for my custom types? \dT provides information such as schema, name, and description for all the registered types and custom types. I would like to provide a description for each custom type I create. Thanks, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Custom type
> > Hi,
> >
> > How can I enter description for my custom types?
> >
> > \dT provides information such as schema, name, and description for
all
> > the registered types and custom types. I would like to provide a
> > description for each custom type I create.
> >
> > Thanks,
> >
> >
> > --
> > Daniel CAUNE
> > Ubisoft Online Technology
> > (514) 4090 2040 ext. 5418
> >
>
> Daniel,
>
> From the \h command in psql:
>
> rnd=# \h comment
> Command: COMMENT
> Description: define or change the comment of an object
> Syntax:
> COMMENT ON
> {
> TABLE object_name |
> COLUMN table_name.column_name |
> AGGREGATE agg_name (agg_type) |
> CONSTRAINT constraint_name ON table_name |
> DATABASE object_name |
> DOMAIN object_name |
> FUNCTION func_name (arg1_type, arg2_type, ...) |
> INDEX object_name |
> OPERATOR op (leftoperand_type, rightoperand_type) |
> RULE rule_name ON table_name |
> SCHEMA object_name |
> SEQUENCE object_name |
> TRIGGER trigger_name ON table_name |
> TYPE object_name |
> VIEW object_name
> } IS 'text'
>
> I believe this is what you need.
>
> HTH.
>
I see! I was searching an option in the custom type creation statement,
something like:
CREATE TYPE foo (
...
)
DESCRIPTION "something that might be useful";
Thanks for this information!
--
Daniel
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] OUT parameter
Hi, Is there any suggestion against using OUT parameter for local calculation such as using a local variable? CREATE OR REPLACE FUNCTION foo(a IN int, b1 OUT int, b2 OUT int) AS $$ BEGIN FOR (...) LOOP b1 = (...); b2 = (...); END LOOP; END; $$ LANGUAGE PLPGSQL; or for some reasons (performance or whatever other details of implementation), would it be preferable to use local variable and to initialize the OUT parameters at the end? CREATE OR REPLACE FUNCTION foo(a IN int, b1 OUT int, b2 OUT int) AS $$ V_b1 int; V_b2 int; BEGIN FOR (...) LOOP V_b1 = (...); V_b2 = (...); END LOOP; b1 = V_b1; b2 = V_b2; END; $$ LANGUAGE PLPGSQL; Thanks, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] OUT parameter
> > "Owen Jacobson" <[EMAIL PROTECTED]> writes: > > > I'd say there's no problem with this, PROVIDED you can ensure you'll > > > never abort before completing the computation. > > > > Not really an issue in Postgres: we do not support pass-by-reference > > parameters and are unlikely to start doing so. There isn't any way > > that you can affect locals of a calling procedure before you return. > > Then I've misunderstood the semantics of OUT and more importantly INOUT > parameters. Thanks for the correction; I'm reading Daniel Caune's notes > on the docs now. > > -Owen Funny! I started that thread on OUT parameter; that's a kind of circle... :-) -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(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
[SQL] Index on nullable column
Hi, Is an index on a nullable column useful for retrieving rows having that column null? SELECT PlayerID FROM PlayerLoginSession WHERE EndTime IS NULL; Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Index on nullable column
> > Is an index on a nullable column useful for retrieving rows having that > > column null? > > Nope, because IS NULL isn't an indexable operator. > > You can make an end-run around that with a partial index, eg > > create index fooi on foo(f1) where f1 is null > > This can be used to satisfy queries using "where f1 is null", but it's > not any good for any other purpose. > > If you often do "where f1 is null and something-about-f2", > you might get better mileage with > > create index fooi on foo(f2) where f1 is null > > but it's still a very specialized index. > Thanks Tom. I will use such an index even if it's very specialized; OLAP world is somewhat full of specialized index anyway... :-) -- Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Modeling trees with Nested Sets and Nested Intervals
I would like to model some hierarchical (tree) data in PostgreSQL. Where can I find high quality Nested Set (or Nested Interval) source code and documentation? I know this question gets asked a lot. To illustrate the point, here is just one thread from each of the last five years: http://archives.postgresql.org/pgsql-sql/2001-08/msg00242.php http://archives.postgresql.org/pgsql-sql/2002-05/msg00270.php http://archives.postgresql.org/pgsql-general/2003-12/msg00247.php http://archives.postgresql.org/pgsql-general/2004-03/msg00804.php http://archives.postgresql.org/pgsql-sql/2005-04/msg00231.php Luckily, no one has asked this question yet in 2006. :-) I've been scouring the Net for a while now, but I hope there are more resources out there that I haven't stumbled onto yet. Here's what I've found so far: * Static Hierarchies and Binary Fractions in PostgreSQL, by Michael Glaesemann http://www.grzm.com/fornow/archives/2004/07/10/static_hierarchies This is the most complete out-of-the-box solution I've found. It uses binary fractions and nested intervals (well, Manfred Koizar says its more of a Materialized Path model). Lots of handholding, documentation, and functions for everything you would want to do to a tree. Limited to 61 nodes in the first branch, plus other limitations. * Modified "m-vgID method", by OpenACS http://cvs.openacs.org/cvs/openacs-4/packages/acs-kernel/sql/postgresql/ Reported to support 2^31 nodes per level, uses bitstring encoding. * m-vgID method, by Miguel Sofer http://www.utdt.edu/~mig/sql-trees/ Uses base 159 encoding (all latin1 chars). * Joe Celko's SQL for Smarties: Advanced SQL Programming, 2nd Edition Highly recommended book. Joe also has a few articles and mailing list posts floating around the web: http://www.dbmsmag.com/9603d06.html http://archives.postgresql.org/pgsql-sql/2001-11/msg4.php http://archives.postgresql.org/pgsql-sql/2003-01/msg00459.php To be clear, I'm not looking for an adjacency model, materialized path model, contrib/ltree, or connect by. Other resources that have been helpful: http://troels.arvin.dk/db/rdbms/links/#hierarchical http://groups.google.com/group/comp.databases.theory/msg/7b772060322df739 Maybe all this would make a good project on pgfoundry. -- Daniel Browning - Kavod Technologies. Random Fortune: To Perl, or not to Perl, that is the kvetching. -- Larry Wall in <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] "could not open file" issue
Hi, Is there any way to solve the following issue without dropping the table? select count(*) from eventplayerleaveroom; ERROR: could not access status of transaction 3164404766 DETAIL: could not open file "pg_clog/0BC9": No such file or directory Regards, P.S.: PostgreSQL server 8.1.3 -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] "could not open file" issue
> De : Alvaro Herrera [mailto:[EMAIL PROTECTED] > > Daniel Caune wrote: > > Hi, > > > > Is there any way to solve the following issue without dropping the > > table? > > > > select count(*) from eventplayerleaveroom; > > ERROR: could not access status of transaction 3164404766 > > DETAIL: could not open file "pg_clog/0BC9": No such file or directory > > Are the files in pg_clog close to the vicinity of 0BC9? > I don't have any skill in PostgreSQL administration. However I took a look at /var/lib/postgresql/8.1/main/pg_clog and there is no file close to the vicinity of 0BC9 ; the last file in that directory is: -rw--- 1 postgres postgres 221184 2006-04-24 19:27 00C9 A bit far from 0BC9... Do you have any diagnostic? > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] "could not open file" issue
> De : Alvaro Herrera [mailto:[EMAIL PROTECTED] > > Daniel Caune wrote: > > > > > De : Alvaro Herrera [mailto:[EMAIL PROTECTED] > > > > > > Daniel Caune wrote: > > > > > > > > select count(*) from eventplayerleaveroom; > > > > ERROR: could not access status of transaction 3164404766 > > > > DETAIL: could not open file "pg_clog/0BC9": No such file or > directory > > > > > > Are the files in pg_clog close to the vicinity of 0BC9? > > > > I don't have any skill in PostgreSQL administration. However I took a > > look at /var/lib/postgresql/8.1/main/pg_clog and there is no file > > close to the vicinity of 0BC9 ; the last file in that directory is: > > > > -rw--- 1 postgres postgres 221184 2006-04-24 19:27 00C9 > > > > A bit far from 0BC9... Do you have any diagnostic? > > I'd say you have a corrupted table. How corrupted I don't know. You > could try extracting a portion of the table, playing with LIMIT/OFFSET > to find out the exact records that are corrupted. > Yes, I tried playing with the LIMIT clause, and LIMIT 90 is the better I can pass... :-( > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] "could not open file" issue
> De : [EMAIL PROTECTED] [mailto:pgsql-sql- > [EMAIL PROTECTED] De la part de Andrew Sullivan > > On Mon, Apr 24, 2006 at 12:17:07PM -0400, Daniel Caune wrote: > > Hi, > > > > Is there any way to solve the following issue without dropping the > > table? > > I doubt you'll be able to drop the table. I think you have some sort > of corruption. Assuming your hardware is good, you maybe oughta take > this over to -general to see if the wizards can identify your > problem. (But check your hardware first.) > It seems that was possible: I tried first to truncate the table (it passed), and finally I tried to drop the table (it also passed). Then I created the table. I'm not sure that it fixes my problem. I modified my fstab file so that Linux checks my file system's health on the next boot. > > -- > Andrew Sullivan | [EMAIL PROTECTED] > "The year's penultimate month" is not in truth a good way of saying > November. > --H.W. Fowler ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] keeping last 30 entries of a log table
> I need to write a function which inserts a log entry in a log table and > only > keeps the last 30 records. I was thinking of using a subquery similar to > the > following: > > insert into log (account_id, message) values (1, 'this is a test); > delete from log where account_id = 1 and id not in ( select id from log >where account_id = 1 order by timestamp desc limit 30); > > I'm wondering if there is a more performance oriented method of doing the > delete that I'm not thinking of. > Depending on whether id is a kind of auto-incremented column that never cycles, I would suggest something like: DELETE FROM log WHERE account_id = 1 AND id < ( SELECT MIN(id) FROM log WHERE account_id = 1 ORDER BY timestamp DESC LIMIT 30); I think there will be a performance difference with your method when the number of records to be deleted is huge. -- Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] keeping last 30 entries of a log table
> >> insert into log (account_id, message) values (1, 'this is a test); > >> delete from log where account_id = 1 and id not in ( select id from log > >>where account_id = 1 order by timestamp desc limit 30); > >> > >> I'm wondering if there is a more performance oriented method of doing > the > >> delete that I'm not thinking of. > >> > > > > Depending on whether id is a kind of auto-incremented column that never > cycles, I would suggest something like: > > > > DELETE FROM log > > WHERE account_id = 1 > >AND id < ( > > SELECT MIN(id) > >FROM log > >WHERE account_id = 1 > >ORDER BY timestamp DESC > > LIMIT 30); > > > > I think there will be a performance difference with your method when the > number of records to be deleted is huge. > > Thanks Daniel, I'll try and benchmark them both and see if < turns out to > be > faster than NOT IN. I guess there's no way to get around the subselect > though. > Column id should be indexed indeed. Anyway, I'm not sure about any performance improvement using that last method, as the most consuming operation might be the DELETE operation, not really the SELECT operation, when dealing with a huge volume of data. -- Daniel ---(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
[SQL] Documentation Generator for pl/pgsql
Hi, Are you aware of any documentation generator for PL/PgSQL? I’m used to write function documentation using a javadoc style. I’m aware of a tool plsqldoc that generated documentation for PL/SQL code, whose syntax is closed to PL/PgSQL. Does someone use this tool for PL/PgSQL? Thanks, -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613
[SQL] Constraint UNIQUE on a column not case sensitive
Hi,
I would like to find an efficient solution for adding/implementing a constraint
UNIQUE on a VARCHAR column not case sensitive:
ALTER TABLE MyTable
ADD CONSTRAINT UNQ_MyTable_MyColumn
UNIQUE (lower(MyColumn)); -- invalid syntax
The idea is to have an index on that column, in a not case sensitive form, i.e.
lower(MyColumn).
SELECT *
FROM MyTable
WHERE lower(MyColumn) = lower('...');
I don't know how to add such a constraint on MyTable except by defining a
trigger on INSERT clause and checking whether lower(:NEW.MyColumn) has been
already inserted in MyTable.
Is there better and more efficient way to do that?
Regards,
--
Daniel
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Constraint UNIQUE on a column not case sensitive
> -Message d'origine- > De : Michael Glaesemann [mailto:[EMAIL PROTECTED] > Envoyé : samedi 1 juillet 2006 10:01 > À : Daniel CAUNE > Cc : [email protected] > Objet : Re: [SQL] Constraint UNIQUE on a column not case sensitive > > > On Jul 1, 2006, at 22:47 , Daniel CAUNE wrote: > > > The idea is to have an index on that column, in a not case > > sensitive form, i.e. lower(MyColumn). > > I think you're really close. Try > > CREATE UNIQUE INDEX ci_mycolumn_idx ON mytable(lower(mycolumn)); > > Does that do what you're looking for? > > Michael Glaesemann > grzm seespotcode net > > Yes, indeed! I'm stupid! I was searching a constraint form while the creation of an UNIQUE index makes the job too! The following form is not valid: ALTER TABLE MyTable ADD CONSTRAINT UNQ_MyTable_MyColumn UNIQUE (lower(MyColumn)); But your form makes the expected job: CREATE UNIQUE INDEX IDX_MyTable_MyColumn ON MyTable (lower(MyColumn)); Thanks, -- Daniel CAUNE ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Local variable and column name conflict
Hi, Is there a way to specify a local variable/parameter within a query where a column has the same name than the local variable/parameter? Example: CREATE OR REPLACE FUNCTION foo(i IN int) RETURNS void AS $$ BEGIN UPDATE bar SET i = i; // column i = parameter i END; $$ LANGUAGE PLPGSQL; For instance, Ingres suggests prefixing local variable/parameter with ":" in the query: UPDATE bar SET i = :i; I can continue using the de facto Oracle's naming convention (P_parameter and V_local_variable) anyway. That's not a big deal. Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Invalid memory alloc request size
Hi, I'm using PostgreSQL 8.1.4 on a Linux 2.6.8-2-686-smp machine, 2 Go memory. I have a strange error "invalid memory alloc request size", when I perform the following query: => select * from player where lower(username) = 'wario'; ERROR: invalid memory alloc request size 1918988375 where player contains a bit more than 1 million records, username is a character varying(255) not null. I have strictly no idea about what goes wrong here. Whatever operation I try to apply on the column username's value (lower, upper, length) raises such an error. I searched for some similar cases in the pgsql-sql archive but nothing really similar. Any idea? Regards, P.S.: I don't have this problem on other tables containing less data. -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Trigger, record "old" is not assigned yet
Hi, I’ve created a trigger BEFORE INSERT OR UPDATE on a table and, indeed, when the trigger is raised before insertion the record “old” is not assigned. Is there a way to distinguish in the trigger procedure from an insert statement to an update statement? Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613
[SQL] PostgreSQL server terminated by signal 11
Hi, My PostgreSQL server running on a Linux machine is terminated by signal 11 whenever I try to create some indexes on a table, which contains quite a lot of data. However I succeeded in creating some other indexes without having the PostgreSQL server terminated: agora=> CREATE INDEX IDX_GSLOG_EVENTTIME agora-> ON GSLOG_EVENT (EVENT_DATE_CREATED); CREATE INDEX Time: 152908.797 ms agora=> explain analyze select max(event_date_created) from gslog_event; QUERY PLAN -- Result (cost=3.80..3.81 rows=1 width=0) (actual time=0.218..0.221 rows=1 loops=1) InitPlan -> Limit (cost=0.00..3.80 rows=1 width=8) (actual time=0.197..0.200 rows=1 loops=1) -> Index Scan Backward using idx_gslog_eventtime on gslog_event (cost=0.00..39338251.59 rows=10348246 width=8) (actual time=0.188..0.188 rows=1 loops=1) Filter: (event_date_created IS NOT NULL) Total runtime: 0.324 ms (6 rows) Time: 41.085 ms agora=> CREATE INDEX IDX_GSLOG_EVENT_SPREAD_PROTOCOL_NAME agora-> ON GSLOG_EVENT (EVENT_DATE_CREATED) agora-> WHERE EVENT_NAME::text <> 'player-login'::text agora-> AND PLAYER_USERNAME IS NOT NULL agora-> AND GAME_CLIENT_VERSION IS NULL; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. The PostgreSQL log file doesn’t give more information about what went wrong, except that the server process has been terminated: LOG: server process (PID 22270) was terminated by signal 11 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing FATAL: the database system is starting up LOG: database system was interrupted at 2006-07-27 15:29:27 GMT LOG: checkpoint record is at 249/179D44A8 LOG: redo record is at 249/179D44A8; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 543712876; next OID: 344858 LOG: next MultiXactId: 2; next MultiXactOffset: 3 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 249/179D44EC LOG: record with zero length at 249/179E4888 LOG: redo done at 249/179E2DFC LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database "postgres" I checked the memory installed on the machine, running memtest86 during more than one day; no error found. I checked bad blocks on every hard drive installed in this machine, using e2fsck -c /dev/hdxx; no bad block found. I’ve already dropped the table, inserted data, and tried to create all the indexes. The server systematically crashed when creating some specific indexes. The only idea I have for the moment would be to setup another machine with the same database environment. Other idea(s)? Thanks -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613
Re: [SQL] PostgreSQL server terminated by signal 11
> De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : jeudi, juillet 27, 2006 16:06 > À : Daniel Caune > Cc : [email protected] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > My PostgreSQL server running on a Linux machine is terminated by signal > > 11 whenever I try to create some indexes on a table, which contains > > quite a lot of data. > > Judging from your examples it's got something to do with the partial > index WHERE clause. What PG version is this exactly? If you leave out > different parts of the WHERE, does it still crash? Does the crash > happen immediately after you give the command, or does it run for > awhile? It might be worth getting a stack trace from the failure > (best way is to attach to the running backend with gdb, provoke the > crash, and do "bt" --- search for "gdb" in the archives if you need > details). > > regards, tom lane The postgres server version is 8.1.4. Yes, if leave the WHERE clause a simple index, I don't encounter any problem: CREATE INDEX IDX_GSLOG_EVENTTIME ON GSLOG_EVENT (EVENT_DATE_CREATED); Anyway, I'm not sure, Tom, that is only related to the WHERE clause as crash occur with composite index too, such as: CREATE INDEX IDX_GSLOG_EVENT_PLAYER_EVENT ON GSLOG_EVENT (PLAYER_USERNAME, EVENT_NAME); The crash may happen a while after sending the command. For example, supposing I reboot the Linux machine and I immediately run the command (i.e. most of memory is unused), it takes more than five minutes before crash occurs. At such time the memory usage is the following (top every second): Mem: 2075860k total, 1787600k used, 288260k free, 6300k buffers Swap: 369452k total,0k used, 369452k free, 1748032k cached When reconnecting to the new postgres respawn, it takes approximately the same time for having it crashing, whatever the number of times I proceed like this. I did some other tests trying to detect any common denominator that may make the postgres server crashing. Here some results are: select max(length(game_client_version)) from gslog_event; => [CRASH] select max(length(game_client_version)) from gslog_event where game_client_version is not null; => [OK, max = 28] select count(*) from gslog_event where length(game_client_version) >= 0; => [OK, count = 4463726] select count(*) from gslog_event where upper(game_client_version) = 'FARCRYPC1.33'; => [OK, count = 576318] select count(*) from gslog_event where lower(player_username) = 'lythanhphu'; => [CRASH] I was thinking about nullable value, but finally, you know what? I have strictly no idea! :-) I'll look at the archive for running postgres with gdb and provide more accurate information. Thanks, -- Daniel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] PostgreSQL server terminated by signal 11
> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : jeudi, juillet 27, 2006 16:06 > À : Daniel Caune > Cc : [email protected] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > My PostgreSQL server running on a Linux machine is terminated by signal > > 11 whenever I try to create some indexes on a table, which contains > > quite a lot of data. > > Judging from your examples it's got something to do with the partial > index WHERE clause. What PG version is this exactly? If you leave out > different parts of the WHERE, does it still crash? Does the crash > happen immediately after you give the command, or does it run for > awhile? It might be worth getting a stack trace from the failure > (best way is to attach to the running backend with gdb, provoke the > crash, and do "bt" --- search for "gdb" in the archives if you need > details). > > regards, tom lane Quite a long time I didn't use gdb! :-) Anyway I proceed as described hereafter; correct me if I was wrong. > ps -eaf | grep postgres postgres 2792 2789 0 21:50 pts/200:00:00 su postgres postgres 2793 2792 0 21:50 pts/200:00:00 bash postgres 2902 1 7 22:17 ?00:01:10 postgres: dbo agora [local] idle postgres 2952 1 2 22:32 ?00:00:00 /usr/lib/postgresql/8.1/bin/postmaster -D /var/lib/postgresql/8.1/main -c unix_socket_directory=/var/run/postgresql -c config_file=/etc/postgresql/8.1/main/postgresql.conf -c hba_file=/etc/postgresql/8.1/main/pg_hba.conf -c ident_file=/etc/postgresql/8.1/main/pg_ident.conf postgres 2954 2952 0 22:32 ?00:00:00 postgres: writer process postgres 2955 2952 0 22:32 ?00:00:00 postgres: stats buffer process postgres 2956 2955 0 22:32 ?00:00:00 postgres: stats collector process I connected to the postgres server using psql and I retrieved the backend pid by executing the statement "SELECT pg_backend_pid();" I started gdb under the UNIX account postgres and I attached the backend process providing the pid returned by the statement. I run the command responsible for creating the index and I entered "continue" in gdb for executing the command. After a while, the server crashes: Program received signal SIGSEGV, Segmentation fault. 0x08079e2a in slot_attisnull () (gdb) Continuing. Program terminated with signal SIGSEGV, Segmentation fault. The program no longer exists. I can't do "bt" since the program no longer exists. How can I provide more information, stack trace, and so on? -- Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] PostgreSQL server terminated by signal 11
> -Message d'origine- > De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > De la part de Tom Lane > Envoyé : jeudi 27 juillet 2006 19:26 > À : Daniel Caune > Cc : [email protected]; [email protected] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > I run the command responsible for creating the index and I entered > "continue" in gdb for executing the command. After a while, the server > crashes: > > > Program received signal SIGSEGV, Segmentation fault. > > 0x08079e2a in slot_attisnull () > > (gdb) > > Continuing. > > > Program terminated with signal SIGSEGV, Segmentation fault. > > The program no longer exists. > > > I can't do "bt" since the program no longer exists. > > I think you typed one carriage return too many and the thing re-executed > the last command, ie, the continue. Try it again. > OK, I'll try that tomorrow morning. Perhaps can I set a conditional breakpoint to function slot_attisnull when parameter slot is null (or slot->tts_tupleDescriptor is null). > The lack of arguments shown for slot_attisnull suggests that all we're > going to get is a list of function names, without line numbers or > argument values. If that's not enough to figure out the problem, can > you rebuild with --enable-debug to get a more useful stack trace? > Well, I installed PostgreSQL using apt-get but that won't be a problem to get the source from the CVS repository and to build a postgres binary using the option you provide to me. Just let me the time to do that. :-) Thanks, -- Daniel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PostgreSQL server terminated by signal 11
> -Message d'origine- > De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > De la part de D'Arcy J.M. Cain > Envoyé : jeudi 27 juillet 2006 19:49 > À : Daniel Caune > Cc : [EMAIL PROTECTED]; [email protected]; pgsql- > [EMAIL PROTECTED] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > On Thu, 27 Jul 2006 19:00:27 -0400 > "Daniel Caune" <[EMAIL PROTECTED]> wrote: > > I run the command responsible for creating the index and I entered > "continue" in gdb for executing the command. After a while, the server > crashes: > > > > Program received signal SIGSEGV, Segmentation fault. > > 0x08079e2a in slot_attisnull () > > That's a pretty small function. I don't see much room for error. This > diff in src/backend/access/common/heaptuple.c seems like the most > likely place to catch it. > > RCS file: /cvsroot/pgsql/src/backend/access/common/heaptuple.c,v > retrieving revision 1.110 > diff -u -p -u -r1.110 heaptuple.c > --- heaptuple.c 14 Jul 2006 14:52:16 - 1.110 > +++ heaptuple.c 27 Jul 2006 23:37:54 - > @@ -1470,8 +1470,13 @@ slot_getsomeattrs(TupleTableSlot *slot, > bool > slot_attisnull(TupleTableSlot *slot, int attnum) > { > - HeapTuple tuple = slot->tts_tuple; > - TupleDesc tupleDesc = slot->tts_tupleDescriptor; > + HeapTuple tuple; > + TupleDesc tupleDesc; > + > + assert(slot != NULL); > + > + tuple = slot->tts_tuple; > + tupleDesc = slot->tts_tupleDescriptor; > > /* > * system attributes are handled by heap_attisnull > > Of course, you still have to find out what's calling it with slot set > to NULL if that turns out to be the problem. It may also be that slot > is not NULL but set to garbage. You could also add a notice there. > Two, in fact. One to display the address of slot and one to display > the value of slot->tts_tuple or slot->tts_tupleDescriptor. If the > first shows a non NULL value and the second causes your crash that > tells you that the value of slot is probably trashed before > calling the function. > Yes, I was afraid to go that deeper, but it's time! :-)) Actually it seems, from the source code, that a null slot->tts_tuple won't lead to a segmentation fault in function slot_attisnull, while slot and slot->tts_tupleDescriptor will. I will trace the function trying to discover what goes wrong behind the scene. > Do this in conjunction with Tom Lane suggestion of "--enable-debug" for > more information. > OK -- Daniel ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] PostgreSQL server terminated by signal 11
> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : jeudi, juillet 27, 2006 19:26 > À : Daniel Caune > Cc : [email protected]; [email protected] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > I run the command responsible for creating the index and I entered > "continue" in gdb for executing the command. After a while, the server > crashes: > > > Program received signal SIGSEGV, Segmentation fault. > > 0x08079e2a in slot_attisnull () > > (gdb) > > Continuing. > > > Program terminated with signal SIGSEGV, Segmentation fault. > > The program no longer exists. > > > I can't do "bt" since the program no longer exists. > > I think you typed one carriage return too many and the thing re-executed > the last command, ie, the continue. Try it again. > You were right. Program received signal SIGSEGV, Segmentation fault. 0x08079e2a in slot_attisnull () (gdb) bt #0 0x08079e2a in slot_attisnull () #1 0x0807a1d0 in slot_getattr () #2 0x080c6c73 in FormIndexDatum () #3 0x080c6ef1 in IndexBuildHeapScan () #4 0x0809b44d in btbuild () #5 0x0825dfdd in OidFunctionCall3 () #6 0x080c4f95 in index_build () #7 0x080c68eb in index_create () #8 0x08117e36 in DefineIndex () #9 0x081db4ee in ProcessUtility () #10 0x081d8449 in PostgresMain () #11 0x081d99d5 in PortalRun () #12 0x081d509e in pg_parse_query () #13 0x081d6c33 in PostgresMain () #14 0x081aae91 in ClosePostmasterPorts () #15 0x081ac14c in PostmasterMain () #16 0x08168f22 in main () -- Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PostgreSQL server terminated by signal 11
> De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : vendredi, juillet 28, 2006 09:38 > À : Daniel Caune > Cc : [email protected]; [email protected] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > Program received signal SIGSEGV, Segmentation fault. > > 0x08079e2a in slot_attisnull () > > (gdb) bt > > #0 0x08079e2a in slot_attisnull () > > #1 0x0807a1d0 in slot_getattr () > > #2 0x080c6c73 in FormIndexDatum () > > #3 0x080c6ef1 in IndexBuildHeapScan () > > #4 0x0809b44d in btbuild () > > #5 0x0825dfdd in OidFunctionCall3 () > > #6 0x080c4f95 in index_build () > > #7 0x080c68eb in index_create () > > #8 0x08117e36 in DefineIndex () > > Hmph. gdb is lying to you, because slot_getattr doesn't call > slot_attisnull. > This isn't too unusual in a non-debug build, because the symbol table is > incomplete (no mention of non-global functions). > > Given that this doesn't happen right away, but only after it's been > processing for awhile, we can assume that FormIndexDatum has been > successfully iterated many times already, which seems to eliminate > theories like the slot or the keycol value being bogus. I'm pretty well > convinced now that we're looking at a problem with corrupted data. Can > you do a SELECT * FROM (or COPY FROM) the table without error? > > regards, tom lane The statement "copy gslog_event to stdout;" leads to "ERROR: invalid memory alloc request size 4294967293" after awhile. (...) 354964834 2006-07-19 10:53:42.813+00 (...) 354964835 2006-07-19 10:53:44.003+00 (...) ERROR: invalid memory alloc request size 4294967293 I tried then "select * from gslog_event where gslog_event_id >= 354964834 and gslog_event_id <= 354964900;": 354964834 | 2006-07-19 10:53:42.813+00 | (...) 354964835 | 2006-07-19 10:53:44.003+00 | (...) 354964837 | 2006-07-19 10:53:44.113+00 | (...) 354964838 | 2006-07-19 10:53:44.223+00 | (...) (...) (66 rows) The statement "select * from gslog_event;" leads to "Killed"... Ouch! The psql client just exits (the postgres server crashes too)! The statement "select * from gslog_event where gslog_event_id <= 354964834;" passed. I did other tests on some other tables that contain less data but that seem also corrupted: copy player to stdout ERROR: invalid memory alloc request size 1918988375 select * from player where id >=771042 and id<=771043; ERROR: invalid memory alloc request size 1918988375 select max(length(username)) from player; ERROR: invalid memory alloc request size 1918988375 select max(length(username)) from player where id <= 771042; max - 15 select max(length(username)) from player where id >= 771050; max - 15 select max(length(username)) from player where id >= 771044 and id <= 771050; max - 13 Finally: select * from player where id=771043; ERROR: invalid memory alloc request size 1918988375 select id from player where id=771043; id 771043 (1 row) agora=> select username from player where id=771043; ERROR: invalid memory alloc request size 1918988375 I'm also pretty much convinced that there are some corrupted data, especially varchar row. Before dropping corrupted rows, is there a way to read part of corrupted data? Thanks Tom for your great support. I'm just afraid that I wasted your time... Anyway I'll write a FAQ that provides some information about this kind of problem we have faced. Regards, -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Recording how a table is used
I'm trying to record the results of a select statement into a separate table. I need the information selected, the column it was stored in, the table it was stored in, and the query that selected it. Everything I've read so far says that triggers can't trigger from select statements, and they don't have a variable that stores the query it triggered from. I would be happy with partial solutions since my other option is a complicated perl script that has to parse the query statement to figure out where it is looking for the information Also, my next step is to do the same for as many types of statements as possible, so if anyone knows how to retrieve the insert, update, or delete statement sent to the database from inside the trigger, that would be useful as well. Here is the table I'm trying to fill, in case it is useful create table sql_query_data_log ( id serial, create_ time timestamp DEFAULT now(), query varchar, table text, column text, data varchar );
[SQL] Remove my e-mail
Hi!, I don't want to receive more emails from postgresql. Please remove my email from your delivery list. Thanks -- Daniel Guedes
