Re: [SQL] Comparing two tables of different database
On 05/01/2009 11:55 AM, John Zhang wrote: Hi Nicholas, The query is across database query. dblink is needed for that task. Hope it helps, John On Thu, Apr 30, 2009 at 3:07 PM, Edward W. Rouse ero...@comsquared.com mailto:ero...@comsquared.com wrote: Can’t you use this? select name from database2.sr_1 where name not in (select name from database2.pr_1); My test database VM isn’t running so I can’t test it, but I seem to remember that that’s how I did it for a few queries of that type. This is assuming the 2 databases are running on the same machine, like the way there is template0 as the default and you add addition databases to the same ‘instance’. If you are talking about 2 different database servers, then I have no idea. Edward W. Rouse How do you formulate the query using dblink? Thanks Wei -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Performance problem with row count trigger
On 04/02/2009 03:32 PM, Tom Lane wrote: Tony Cebzanov tony...@andrew.cmu.edu writes: What I want to do is update the assoc_count field in the dataset table to reflect the count of related records in the assoc field. To do so, I added the following trigger: CREATE OR REPLACE FUNCTION update_assoc_count_insert() RETURNS TRIGGER AS ' BEGIN UPDATE dataset SET assoc_count = assoc_count + 1 WHERE dataset_id = NEW.dataset_id; RETURN NEW; END ' LANGUAGE plpgsql; CREATE TRIGGER assoc_update_assoc_count_insert AFTER INSERT ON assoc FOR EACH ROW EXECUTE PROCEDURE update_assoc_count_insert(); There is basically no way that this is going to not suck :-(. In the first place, using an AFTER trigger means that each update queues an AFTER trigger update event that has to be fired at statement or transaction end. In the second place (as Craig correctly noted) this results in a separate update to the count-table row for each inserted row, which tremendously bloats the count table with dead tuples. In the third place, if you have any concurrency of insertions, it disappears because all the inserters need to update the same count row. If you dig in the pgsql-hackers archives, you will find that the original scheme for this was to have each transaction accumulate its total number of insertions minus deletions for a table in local memory, and then insert *one* delta row into the count table just before transaction commit. I don't think it's possible to do that with just user-level triggers (not least because we haven't got ON COMMIT triggers); it would have to be a C-code addition. The various blog entries you cite are non-peer-reviewed oversimplifications of that design. Digging around, the oldest description I can find of this idea is http://archives.postgresql.org/pgsql-hackers/2003-09/msg00387.php although there are more recent threads rehashing the topic. One point I don't recall anyone mentioning is that the stats subsystem now implements a fairly large subset of this work already, namely the initial data accumulation. So you could imagine plugging something into that to send the deltas to a table in addition to the stats collector. regards, tom lane So, basically other than reading from pg_class table about the tuple count, there isn't a good way to optimize the COUNT(*)? Thanks Wei
[SQL] How do I optimize this?
Hi all. I have the following scenario: A table T (int t1; int t2; ... int t10; int tkey) A table D (int da; int db), And I have the following query update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = count) as b where tkey = value; update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = count) as b where tkey = value; ... update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = count) as b where tkey = value; The queries are run on the same value. Is there anyway to optimize this??? Thanks Wei -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How do I optimize this?
I made a mistake in the queries: They should be update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 1) as b where tkey = value; update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 2) as b where tkey = value; ... update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 10) as b where tkey = value; Thanks Wei On 03/17/2009 05:43 PM, Wei Weng wrote: Hi all. I have the following scenario: A table T (int t1; int t2; ... int t10; int tkey) A table D (int da; int db), And I have the following query update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = count) as b where tkey = value; update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = count) as b where tkey = value; ... update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = count) as b where tkey = value; The queries are run on the same value. Is there anyway to optimize this??? Thanks Wei -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Vacation days
On Monday 25 June 2007 15:22, Susan Young wrote: Hi Wei, That's OK - Enjoy! Susan Wei Weng wrote: Can I take next week off? Thanks! Wei hi, susan, a change of plan. :) Instead of the whole week, I just wanted to take next monday and tuesday off. Thanks! Wei ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] [Fwd: 47G file]
On Friday 22 June 2007 12:23, Alexandre Parizot wrote:
Wei, Can you check the database error messages in the message Main
Log. You can find the system information in the email System Info 7.1.
Alex.
This is the script that caused the error in 7.1.0.0. (Fixed already)
The script in CVS is correct. This is the changelog regarding this error:
revision 1.6
date: 2007-02-05 23:21:05 +; author: alex; state: Exp; lines: +1 -1
* Merge from R7100_bugfixes_branch
* Bug:
Unicast delivery throw a DB exception when archiving the
transmission.
Fix:
The store procedure kcFTULog() use an undeclare variable (_tmp).
Wei
--
-- File Transmission Unicast stored procedures
--
CREATE OR REPLACE FUNCTION kcFTUMarkUnicastSites(
CHAR(36),
INTEGER,
CHAR(1) )
RETURNS VOID AS '
DECLARE
_tuuid ALIAS FOR $1;
_failAttempts ALIAS FOR $2;
_allowMulticast ALIAS FOR $3;
_isBroadcast CHAR(1);
_ackMode CHAR(1);
_lastSent TIMESTAMP;
_cnt INTEGER;
BEGIN
SELECT IsBroadcast, AckReportMode, LastTxEndTime INTO _isBroadcast,
_ackMode, _lastSent
FROM SendTransmissions WHERE TransmissionUUID = _tuuid;
IF( _allowMulticast = ''F'' OR _ackMode ''N'') THEN
BEGIN
SELECT COUNT(TransmissionUUID) INTO _cnt FROM
SendTransmissionSites WHERE TransmissionUUID=_tuuid;
IF( _cnt = 0 AND _isBroadcast = ''T'' ) THEN
BEGIN
INSERT INTO SendTransmissionSites (TransmissionUUID,
SiteID, Unicast, DeliveryStatus)
SELECT _tuuid, SiteID, ''T'', ''N''
FROM UnicastSendSites
JOIN ReceiveSites AS R ON R.ReceiveSiteID =
UnicastSendSites.ReceiveSiteID
WHERE UnicastSendSites.Disable = ''F'' AND
UnicastSendSites.AllowMulticast = _allowMulticast;
END;
END IF;
IF( _cnt 0 AND _isBroadcast ''T'' ) THEN
BEGIN
-- update the the site IDs in SentTransmissionSites
UPDATE SendTransmissionSites
SET Unicast = ''T''
WHERE TransmissionUUID = _tuuid
AND Unicast ''T'' AND DeliveryStatus = ''N''
AND SiteID IN
(SELECT SiteID
FROM UnicastSendSites
JOIN ReceiveSites AS R ON R.ReceiveSiteID =
UnicastSendSites.ReceiveSiteID
WHERE UnicastSendSites.Disable = ''F'' AND
UnicastSendSites.AllowMulticast = _allowMulticast);
END;
END IF;
END;
END IF;
-- for sites that acknowledged the receiving of the file set Unicast =
''F''
IF( _ackmode = ''A'' ) THEN
BEGIN
-- report always
UPDATE SendTransmissionSites
SET Unicast = ''F''
WHERE TransmissionUUID = _tuuid
AND SiteID IN
(SELECT SiteID
FROM TransmissionAck AS A
WHERE A.Status IN (''V'', ''C'', ''D'', ''N'')
AND A.TransmissionUUID = _tuuid);
END;
ELSIF( _ackmode = ''F'' AND _lastSent IS NOT NULL ) THEN
BEGIN
-- report on incomplete
UPDATE SendTransmissionSites
SET Unicast = ''F''
WHERE TransmissionUUID = _tuuid
AND SiteID NOT IN
(SELECT SiteID
FROM TransmissionAck AS A
WHERE A.Status IN (''E'', ''I'')
AND A.TransmissionUUID = _tuuid
AND A.ReceivedOn _lastSent);
END;
END IF;
-- skip sites which fail too often
IF( _failAttempts 0 ) THEN
BEGIN
-- report always
UPDATE SendTransmissionSites
SET DeliveryStatus = ''K''
WHERE TransmissionUUID = _tuuid
AND Unicast = ''T''
AND SiteID IN
(SELECT SiteID
FROM UnicastSendSites
JOIN ReceiveSites AS R ON R.ReceiveSiteID =
UnicastSendSites.ReceiveSiteID
WHERE UnicastSendSites.FailedConnectionCount
_failAttempts
AND UnicastSendSites.LastConnectionTime
CURRENT_TIMESTAMP + INTERVAL ''4 hours'');
END;
END IF;
RETURN;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION kcFTULog(
CHAR(36),
CHAR(36),
INTEGER,
INTEGER,
VARCHAR(255),
INTEGER,
INTEGER,
DECIMAL,
DECIMAL,
CHAR(1),
VARCHAR(255),
INTEGER,
DECIMAL,
DECIMAL)
RETURNS VOID AS '
DECLARE
_logid ALIAS FOR $1;
_tuuid ALIAS FOR $2;
_tid ALIAS FOR $3;
[SQL] Question on interval
Hi all. How do I write a query that converts an interger to the interval type? Like convert integer 10 to INTERVAL '10 seconds'? The integer is a column in a table though, so it is more like convert integer tbl.theInteger to INTERVAL 'tbl.theInteger seconds. Thanks! Wei ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] How do I quit in the middle of a SQL script?
Say if I want to add a small snip of code in front of the sql script generated by the pg_dump, to check for something then if the condition doesn't match, the script terminates right away. (Without actually doing the restoring stuff that the following large chunk is supposed to do) Can I do that? And is it a good idea to add arbitrary code to the database dump sql script? Thanks! Wei ---(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
[SQL] How do I do this?
I have a table with column filepath with contents that look like the following: filepath == /var/log/foo /var/log/bar /var/cache/foo /var/cache/bar /var/foo /var/bar Is there anyway to retrieve the directory information only regarding those filepaths? So that I can get /var/log /var/cache /var as the result of a query? Thanks! (I am using PostgreSQL 7.4) Wei ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SQL] Cast NULL into Timestamp?
I have a table create table temp ( tempdatetimestamp, tempnamevarchar(10) ); And I tried to insert the following: insert into table temp (tempname, tempdate) select distinct 'tempname', null from some_other_relevant_table; And I got an error that says column tempdate is of type timestamp ... but expression is of type text... will need to rewrite or cast the expression. I really need the distinct. Is there anyway to cast this NULL into a timestamp or any other workarounds? Thanks Wei ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How do you compare (NULL) and (non-NULL)?
Bruno Wolff III wrote: On Tue, Oct 26, 2004 at 16:23:20 -0400, Wei Weng [EMAIL PROTECTED] wrote: In the following query SELECT Parent FROM Channels ORDER BY Parent ASC; If I have a couple of (NULL)s in the field [Parent], they will be listed at the bottom of the query result. Is it because PostgreSQL considers (NULL) as the biggest value? If I run the same query under MSSQL Server 2000, I get the exact opposite result regarding the order of (NULL)s and (non-NULL) values. They are listed at the very beginning of the query result. If the order matters, you can order by IS NULL or IS NOT NULL. ---(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 How do I write that? Thanks Wei ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] How do you compare (NULL) and (non-NULL)?
In the following query SELECT Parent FROM Channels ORDER BY Parent ASC; If I have a couple of (NULL)s in the field [Parent], they will be listed at the bottom of the query result. Is it because PostgreSQL considers (NULL) as the biggest value? If I run the same query under MSSQL Server 2000, I get the exact opposite result regarding the order of (NULL)s and (non-NULL) values. They are listed at the very beginning of the query result. Thanks Wei ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] LIKE operator and string comparison
I used the following SQL code to match '\foo\bar' SELECT text FROM test WHERE text LIKE 'foo%' But if I choose to use string comparison, instead of 4 escape characters, I only need 2. SELECT text FROM test WHERE text = '\\foo\\bar' Why is that? I am using PostgreSQL 7.4, and the SQL code was entered through psql. Thanks! Wei ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] LIKE operator and string comparison
Tom Lane wrote: Wei Weng [EMAIL PROTECTED] writes: But if I choose to use string comparison, instead of 4 escape characters, I only need 2. Why is that? Backslash is an escape character for LIKE. regards, tom lane What about in regular strings? I do need to use backslash to escape the other backslash in order to get the '\'. Why isn't that the case in LIKE operation? Thanks Wei ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] postgreSQL editors
Michael Glaesemann wrote: On Dec 30, 2003, at 10:05 AM, beyaRecords - The home Urban music wrote: Hi, can anyone recommend a good editor for postgreSQl wich wil enable me to create functions/stored procedures? I am currently running version 7.4.1 on OS X 10.3 If you're looking for an editor to write arbitrary SQL and support color syntax highlighting, you probably can't go wrong with BBEdit. You can get the ANSI SQL glossary for highlighting, and although it won't pick up PostgreSQL-specific extensions to SQL, it will highlight the majority of the syntax. Also has great grep (including savable search patterns), Applescript, FTP, CVS, and shell script support, if those things interest you. I write most of my SQL in it before feeding the file to psql or just copying and pasting the commands into phppgadmin or psql directly. Michael Glaesemann grzm myrealbox com I would vote for emacs/vim if you only want simple stuff such as color syntax highlighting. :) And with some emacs extension, you probably can do much cooler stuff with it too. Wei ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] testing for null value in integer field?
Geoffrey wrote: How does one check for an unset value in an integer field? I've tried such things as: select . where intnumber = '' select .. where intnumber = ? select . where intnumber = NULL Thanks. It is actually WHERE intnumber IS NULL. You don't use operator = to compare with NULLs, you use IS. HTH. Wei ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] How do I convert an interval into integer?
I want to convert an interval (from substraction between two timestamps) into a integer that represents how many seconds that interval has. How do I do that? I am using postgresql 7.3.1 Thanks Wei ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [PERFORM] sql performance and cache
On Sat, 11 Oct 2003, Christopher Kings-Lynne wrote: I have two very similar queries which I need to execute. They both have exactly the same from / where conditions. When I execute the first, it takes about 16 seconds. The second is executed almost immediately after, it takes 13 seconds. In short, I'd like to know why the query result isn't being cached and any ideas on how to improve the execution. snip OK - so I could execute the query once, and get the maximum size of the array and the result set in one. I know what I am doing is less than optimal but I had expected the query results to be cached. So the second execution would be very quick. So why aren't they ? I have increased my cache size - shared_buffers is 2000 and I have doubled the default max_fsm... settings (although I am not sure what they do). sort_mem is 8192. PostgreSQL does not have, and has never had a query cache - so nothing you do is going to make that second query faster. Perhaps you are confusing it with the MySQL query cache? Chris Is there plan on developing one (query cache)? Thanks Wei ---(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] suggestion needed for implementation
I need to create triggers on a number of tables that have full text search
capability. The trigger, basically, needs to call select
set_curcfg('default') first to set the locale , then call tsearch2
function on the text columns in the table to update the index. How can I
implement a trigger that does two different thing (select and tsearch2)
together? I was thinking about making them into one function, say,
function updatetbl(), that does two things together. But then, postgresql
does not allow variable length of arguments. updatetbl can not just take
arbitrary number of arguments to pass on tsearch2.
What can I do here to implement this trigger?
Thanks
Wei
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Bug in psql - Postgresql 7.3.1?
Shouldn't that be UPDATE my_table SET field1 = 'new_value', field2 = 'different_value' WHERE my_table_id = 'key';? Wei On Fri, 3 Oct 2003, John B. Scalia wrote: All, I'm not certain if what I'm trying to do is legal, but if I execute a statement like: UPDATE my_table SET field1='new_value' AND SET field2='different_value' WHERE my_table_id = 'key'; in psql, it reports that it has successfully updated one record. However, the record does not appear to be updated when I subsequently issue a SELECT for that record. I'll buy the fact that I may be using illegal syntax - I'm not SQL expert, but the update says it worked when in fact it did not. Should this have worked or should it have reported an error? For record, this is Postgresql 7.3.1 on Solaris 9. Problem originally seen during a php4 script, but has been duplicated on the psql command line. TIA, John ---(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] tsearch2 question
When I run psql tsearch2.sql, is psql going to substitute $libdir internally with what $libdir really is (in my case, it would be /usr/lib/pgsql)? Thanks Wei On Fri, 26 Sep 2003, Tom Lane wrote: Wei Weng [EMAIL PROTECTED] writes: But then when I do a psql tsearch2.sql, it complains: bash-2.05a$ psql testdb tsearch2.sql ERROR: stat failed on file '$libdir/tsearch2': No such file or directory Where did I do wrong?? Did you do make install after building tsearch2? If you did, maybe it installed tsearch2.so in the wrong place? (pg_config --pkglibdir will tell you what the backend thinks $libdir means.) regards, tom lane ---(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] tsearch2 question
When I ran psql testdb untsearch2.sql I got the following error message: psql:untsearch2.sql:15: ERROR: RemoveAggregate: aggregate stat(tsvector) does not exist I didn't really do anything before this. Only dropped the trigger and gist index I created (in order to use tsearch2), and alter table testtb drop column idxtest tsvector. Where did I do wrong? Thanks Wei ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Capturing pgsql ERRORS/NOTICES to file
George:
Have you tried psql {whatever operations} 2 error_output ?
(for Bash)
Thanks
Wei
On Fri, 26 Sep 2003, George Weaver wrote:
Hi Josh,
Thanks for the reply.
What I am trying to achieve is to have errors go to a file, rather than show
up on the screen.
Is this possible?
George
- Original Message -
From: Josh Berkus [EMAIL PROTECTED]
To: George Weaver [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, September 26, 2003 1:33 PM
Subject: Re: [SQL] Capturing pgsql ERRORS/NOTICES to file
George,
I am in the process of creating a batch file that will update some
functions in a database for a remote user similar to:
psql -o output dbname functionupdate.sql
Is there any way to save any ERROR and NOTICE messages to a file?
The -o option doesn't capture this information.
You have to use command shell redirects.
For example, I commonly do in bash
psql -o output dbname functionupdate.sql out.dump
... which sends all the command responses to a file, allowing me to read
only
the errors on the screen.
See a guide to your shell for more creative redirection.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [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
---(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
[SQL] tsearch2 question
In my tsearch2.sql there are statements like : --dict interface CREATE FUNCTION lexize(oid, text) returns _text as '$libdir/tsearch2' language 'C' with (isstrict); I don't think $libdir is the real value that we want. Do I need to set some special parameters with ./configure (in the root level of the source tree) to replace $libdir with the real library path? Thanks Wei ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] GiST and full text search
Hi all. I have been doing a little research on how to do full text search under postgresql, and GiST seems to be the way to go. But the documentation on this is pretty lacking and I wonder if there are better sources that describe the details on how to implement full text search with GiST. Thanks Wei ---(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] GiST and full text search
The production server uses PostgreSQL 7.3.1 right now, so I am only looking at contrib/tsearch. I read through the README file. It only seems to offer a way to FTS through one 'text' column in the table. How do I create a 'txtidx' column that combines two or even more 'text' columns in the table? Thanks Wei On Mon, 22 Sep 2003, Richard Huxton wrote: On Monday 22 September 2003 16:02, Wei Weng wrote: Hi all. I have been doing a little research on how to do full text search under postgresql, and GiST seems to be the way to go. But the documentation on this is pretty lacking and I wonder if there are better sources that describe the details on how to implement full text search with GiST. Have you looked at tsearch / tsearch2 in the contrib/ directory? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Using sql statements in file
try psql -f filename On Mon, 22 Sep 2003, Suresh Basandra wrote: Hi, I would like to do the following using files: 1. put create database, create tables sql statements in a file and execute through prompt 2. insert or update data that is put in a file Please let me know if there are any examples that lists how sql statements can be put in a file and used. Thanks Suresh Basandra ---(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
[SQL] Extending Datatype
Where can I find a more detailed doc on how to write module for extended datatype? There isn't much about it on techdoc.postgresql.org. (I need to write one for UNIQUEIDENTIFIER.) Thanks! Wei ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] What is wrong with this identification configuration?
SNIP The format of the hba.conf file changed between 7.1 and 7.2. It looks like you are using an old one. After the database field, there is now a user field. To get the same effect as before, use 'all' for the user. I installed PostgreSQL rpm on a fresh installed Redhat 7.3. There is no PostgreSQL 7.1 previously installed so I don't think the pg_hba.conf I was editing is a left-over copy. Specifically, which part of the configuration: local all trust hostall 127.0.0.1 255.255.255.255 trust is wrong and should be fixed accordingly? Thanks Wei ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] iceberg queries
It is a query that looks like SELECT target1, target2... targetn, SUN(t.qty) FROM Table t GROUP BY target1 HAVING SUM(t.qty)=10 You can replace SUM(t.qty)=10 with other aggregate constraints. - Original Message - From: Christoph Haller To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 04, 2003 3:39 AM Subject: Re: [SQL] iceberg queries Does PostgreSQL optimizer handle iceberg queries well? What do you mean by iceberg query ? I've never heard this term. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] iceberg queries
Does PostgreSQL optimizer handle iceberg queries well? Thanks Wei ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Which version is this?
Since which version PostgreSQL is able to do Vacuum Analyze even in the middle of a transaction, namely, insert, delete, update? Thanks Wei
Re: [SQL] help: triggers
It would be better if you could provide the source of that trigger and involved table schemas? Wei - Original Message - From: Tony Simbine [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 29, 2003 8:29 AM Subject: [SQL] help: triggers hello, I'm trying to update a columm on a table with a trigger but it don't work on PostgreSQL 7.2 (i686-pc-linux-gnu, compiled by GCC 2.96). when I update a row the trigger does nothing. what can I do? thanks in advance tony here is my trigger: CREATE FUNCTION update_my_objekt() RETURNS OPAQUE AS ' BEGIN RAISE NOTICE ''My Trigger is updating the table''; IF OLD.status=''Activo'' THEN NEW.tempo := 10; NEW.actual := now(); END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER update_online BEFORE UPDATE ON iobjects FOR EACH ROW EXECUTE PROCEDURE update_my_objekt(); ## ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql
What about a UNIQUEIDENTIFIER type? Is the only way casting it to a CHAR(38)? (Create a domain for it) And does the performance suffer if I do the Domain/create my own data type tricks? Thanks! Wei - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Guy Fraser [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 8:55 PM Subject: Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql Guy Fraser [EMAIL PROTECTED] writes: If you go through the documentation you can also find out how to CREATE your own data TYPE to allow more direct use of non-PostgreSQL data types. Here is an example that will allow input of any datetime data into PostgreSQL : CREATE TYPE datetime AS (datetime timestamptz); I think what you probably really want is CREATE DOMAIN datetime AS timestamptz; or more SQL-spec-compliantly CREATE DOMAIN datetime AS timestamp with time zone; which essentially makes datetime a direct alias for timestamptz. The CREATE TYPE approach makes a rowtype containing one timestamptz column, which isn't really going to act the way you want --- for one thing, none of the predefined functions and operators for type timestamptz will accept it. With the DOMAIN approach, they will. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] Scheduling Events?
Or if you are so paranoid about the stability of crond, you can probably do a check to see whether crond is up when you update the database. If crond is up then update else mail root the error reject the update end This is going to affect the performance dramatically though. - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Achilleus Mantzios [EMAIL PROTECTED] Cc: David Durst [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 24, 2003 10:32 AM Subject: Re: [SQL] Scheduling Events? Achilleus Mantzios [EMAIL PROTECTED] writes: On Fri, 24 Jan 2003, David Durst wrote: Here is the basic problem w/ using CRON in an accounting situation. I can't be sure that cron will always be up when the DB is up, so lets say crond goes down for some random reason (User, System error, Etc..) I you cannot depend on your system to run crond then you should not depend on it to run postgresql either. Indeed. Cron is one of the oldest and most basic Unix daemons. I find it really, really, really hard to believe that any substitute code that anyone might come up with is going to be more reliable than cron. If it makes you feel better, you could institute some cross-checking. For example, have the cron-launched task update a timestamp in some database table whenever it finishes. Then make your user applications check that timestamp when they start up (or every so often) and complain if it's not within the range (now - expected cron frequency, now). That doesn't fix the problem, but at least makes some humans aware of it. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Can I do this?
I want to select some data out of database A, and insert them into database B. Is it possible to do in one SQL query? Thanks Wei
[SQL] Postgresql Bug List?
Is there a bugzilla kind of thing for Postgresql? I would like to help out on the development, but have no idea where to start... Thanks Wei ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SQL] Postgresql Bug List?
Is there a bugzilla kind of thing for Postgresql? I would like to help out on the development, but have no idea where to start... Thanks Wei ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [PERFORM] Does this matter?
Thanks, I noticed that sweet addon and will try to integrate it into our system once 7.3 is officially released. :) btw, do we have a release date yet? Thanks Wei At 11:08 PM 11/3/2002 -0500, you wrote: Wei Weng [EMAIL PROTECTED] writes: Since I need to use a GUID as the primary key, I have to use the char datatype. Try uniqueidentifier: http://archives.postgresql.org/pgsql-announce/2002-07/msg1.php Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] How do you write this query?
I have a table Table test Column |Type| Modifiers ++-- data| integer| not null data1 | character varying(128) | not null data2 | character varying(128) | not null (Note: data is NOT the primary key.) And select * from test returns data | data1 | data2 --+---+--- 1 | foo | bar 2 | greg | bar 3 | pooh | bar 4 | dah | peng I need a query that returns me the data1 that satisfies the logic of the following pseudo code: 1: select data2 into @out from test where data1 = 'pooh' 2: select data1 from test where data2 = @out and data = 3 What do I do? Thanks! -- Wei Weng Network Software Engineer KenCast Inc. ---(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] How do I get rid of these messages?
How do I get rid of the messages like NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey' for table 'test' coming out from stderr when I run psql with my create table script? (I don't want to direct them to /dev/null, since I still want to be able to see the *real* sql error messages) Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Database Design tool
My colleague installed a redhat database 2(which is based on postgresql) onto his redhat 7.3 machine and it came with a very nice java query analyzer tool. It is sort of like the query analyzer in MS SQL Server with the graphical output of the execution plan, quite intuitive. I wonder if I can find a standalone java query analyzer package. Does anyone know? Thanks On Wed, 2002-10-30 at 15:42, Kevin Old wrote: Dan, I use Xpga Java Postgresql client and it is awesome. It's at http://www.kazak.ws/xpg/ Kevin On Wed, 2002-10-30 at 10:30, Dan Hrabarchuk wrote: gASQL is a gnome-db client that looks like it has a lot of promise. The only problem is I've never been able to get the application to run properly. I'm using RedHat 8.0 on my desktop. The last official version does not install properly. If I grab a CVS copy, I go through dependency hell. Has anyone ever actually gotten gASQL to work? Dan On Wed, 2002-10-30 at 06:35, Johannes Lochmann wrote: On Wed, 2002-10-30 at 07:45, Viacheslav N Tararin wrote: Hi, (which list should this go to? I guess it is OT on both...) Can anybody take me reference on Database design tool with PostgreSQL support. Dia and dia2sql (or something similar...) Google knows more :-) HTH Johannes Lochmann ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Kevin Old [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] select question
You can use this query SELECT * FROM T WHERE run = 'a' AND wafer = 1 AND test = 'foo' UNION SELECT * FROM T WHERE run = 'a' AND wafer = 2 AND test = 'foo' UNION SELECT * FROM T WHERE run = 'a' AND wafer = 3 AND test = 'foo' UNION SELECT * FROM T WHERE run = 'a' AND wafer = 3 AND test = 'bar' On Wed, 2002-08-28 at 16:12, george young wrote: [postgreql 7.2, linux] I have a table T with columns run, wafer, and test: T(run text, wafer int, test text) Given a run and a set of wafers, I need the set of tests that match *all* the specified wafers: run wafer test a 1 foo a 2 foo a 3 foo a 3 bar E.g. Given run 'a' and wafers (1,3) I should get one row: foo, since only foo matches both 1 and 3. Given run 'a' and wafers (3) I should get two rows: foo,bar, since both foo and bar match 3. Is there some neat way to do this in a single query? Puzzled, George -- I cannot think why the whole bed of the ocean is not one solid mass of oysters, so prolific they seem. Ah, I am wandering! Strange how the brain controls the brain! -- Sherlock Holmes in The Dying Detective ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] IDENT authentication problem
This is what I did: 1: I reinstalled postgresql RPMs from scratch (I removed all the logs, data files, backup files) 2: su root 3: su postgres 4: psql template1 And here I got the error message: psql: FATAL 1: IDENT authentication failed for user foobar User foobar was an old user I created for the database *before* I wiped out everything and reinstalled the RPMs. It is already gone from my /etc/password. (since I was using IDENT) What could have gone wrong? I must have left the trace of user foobar somewhere in my system but I couldn't find it. Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] concurrent connections is worse than serialization?
On Wed, 2002-08-14 at 05:18, Richard Huxton wrote: On Tuesday 13 Aug 2002 9:39 pm, Wei Weng wrote: I have a testing program that uses 30 concurrent connections (max_connections = 32 in my postgresql.conf) and each does 100 insertions to a simple table with index. It took me approximately 2 minutes to finish all of them. But under the same environment(after delete From test_table, and vacuum analyze), I then queue up all those 30 connections one after another one (serialize) and it took only 30 seconds to finish. Why is it that the performance of concurrent connections is worse than serializing them into one? What was the limiting factor during the test? Was the CPU maxed, memory, disk I/O? No, none of the above was maxed. CPU usage that I paid attention to was at most a 48%. I take it the insert really *is* simple - no dependencies etc. I was testing them using our own (proprietary) scripting engine and the extension library that supports postgresql serializes the queries by simply locking when a query manipulates a PGconn object and unlocking when it is done. (And similiarly, it creates a PGconn object on the stack for each concurrent queries.) I assume you've ruled the application end of things out. What does this mean? Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] concurrent connections is worse than serialization?
On Wed, 2002-08-14 at 10:49, Richard Huxton wrote:
On Wednesday 14 Aug 2002 3:20 pm, Wei Weng wrote:
On Wed, 2002-08-14 at 05:18, Richard Huxton wrote:
On Tuesday 13 Aug 2002 9:39 pm, Wei Weng wrote:
[30 connections is much slower than 1 connection 30 times]
Yeah, but the problem is, say I have 20 users using select on the
database at the same time, and each select takes 10 seconds to finish. I
really can't queue them up (or the last user will reall have to wait for
a long time), can I?
What was the limiting factor during the test? Was the CPU maxed, memory,
disk I/O?
No, none of the above was maxed. CPU usage that I paid attention to was
at most a 48%.
Something must be the limiting factor. One of
- CPU
- Memory
- Disk I/O
- Database (configuration, or design)
- Application
If it's not CPU, is the system going into swap or are you seeing a lot of disk
activity?
I did hear a lot of disk noise when I ran the test. How do I tell if the
system is going into swap?
Is there any system settings I can/should change to make this a little
faster?
I assume you've ruled the application end of things out.
What does this mean?
I mean if you don't actually run the queries, then 30 separate processes is
fine?
If you can provide us with an EXPLAIN of the query and the relevant schema
definitions, we can rule out database design.
This is actually really simple.
A table like
| foo |
|IDVARCHAR(40) | -- primary key
|Name VARCHAR(100)|
And I did an INSERT INTO foo ('some-unique-guid-here', 'Test Name');
So I don't think it is any matter of the database.
Thanks
--
Wei Weng
Network Software Engineer
KenCast Inc.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Is this valid?
I am not sure if this is the right mailing list I talk to. Please let me know if I had violated any unwritten rules. :) I have a global variable PGconn* m_pgconn that is the connection handle for connecting to the postgresql database. Can I access/use the handle from multiple threads? Say I have a thread that does some insertion through this handle/database connection(m_pgconn) and also another thread that do some insertion *on the same table* through this handle(m_pgconn), will that break? Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(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] : [SQL] Is this valid?
I am using C++ and libpq. Thanks Wei -ÓʼþÔ¼þ- ·¢¼þÈË: Robert Treat [mailto:[EMAIL PROTECTED]] ·¢ËÍʱ¼ä: Monday, August 12, 2002 8:10 PM ÊÕ¼þÈË: Wei Weng ³ËÍ: [EMAIL PROTECTED] Ö÷Ìâ: Re: [SQL] Is this valid? I'm going to ask the crazy question of what language/interface are you using to interact with postgres? Based on my interpretation of your question I'd say that won't break (though one of your queries might fail) but then again I may be totally misreading what you wrote... Robert Treat On Mon, 2002-08-12 at 19:21, Wei Weng wrote: I am not sure if this is the right mailing list I talk to. Please let me know if I had violated any unwritten rules. :) I have a global variable PGconn* m_pgconn that is the connection handle for connecting to the postgresql database. Can I access/use the handle from multiple threads? Say I have a thread that does some insertion through this handle/database connection(m_pgconn) and also another thread that do some insertion *on the same table* through this handle(m_pgconn), will that break? Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(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 4: Don't 'kill -9' the postmaster
[SQL] Seeking advice regarding a design problem
I am running postgresql as database backend, and I have some scripts dealing with constant incoming data and then insert these data into the database, in a quite complex way, involving a couple of procedures. But the performance of the database is worse than I had thought. After about 100 times of the script being run, the speed of the insertion slowed down dramatically. But it went back to the regular fast speed after I did a vacuum analyze. how can I redesign the system to avoid the bottleneck? And why is it that postgresql can slow down so much after doing some complex operations? Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Seeking advice regarding a design problem
Forgot to mention, the version of postgresql I am running is 7.1.3. On Fri, 2002-08-02 at 12:16, Wei Weng wrote: I am running postgresql as database backend, and I have some scripts dealing with constant incoming data and then insert these data into the database, in a quite complex way, involving a couple of procedures. But the performance of the database is worse than I had thought. After about 100 times of the script being run, the speed of the insertion slowed down dramatically. But it went back to the regular fast speed after I did a vacuum analyze. how can I redesign the system to avoid the bottleneck? And why is it that postgresql can slow down so much after doing some complex operations? Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] What about this?
Why can't postmaster run VACUUM ANALYZE automatically every once in a while? Since it is a very useful feature... -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] text vs varchar
On Tue, 2002-06-18 at 18:59, Josh Berkus wrote: Wei, Is there any disadvantage of using TEXT datatype as opposed to a VARCHAR datatype with a maximum length, especially when I do searches on them? Yes. You can't index TEXT because it's of potentially unlimited length. -- -Josh Berkus I noticed that it is a characteristics of MS SQL Server, but I did successfully create unique index based on a TEXT field in Postgresql 7.2, while that failed in MS SQL Server (7). Or do they behave the same in this aspect? Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] What is wrong?
Hi there. I wrote a simple postgresql sql function as follows:
create function test() returns integer as '
begin
fixed_path := translate (''/text'', ''\\'', ''/'');
raise notice ''fixed_path:'', fixed_path;
return 1;
end
' language 'plpgsql';
And when I ran it as
pgsqlselect test();
I got:
NOTICE: plpgsql: ERROR during compile of test near line 8
ERROR: unterminated string starting on line 10
What is wrong? (something fishy about translate function?) Could anyone
enlighten me on this?
Thank you very much!
Wei
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] is it possible to get the number of rows of a table?
This should be really easy to implement in a function yourself. And I don't think there is already something similar in pgsql. == Wei Weng Network Software Engineer KenCast Inc. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Frederick Klauschen Sent: Wednesday, September 26, 2001 11:15 AM To: [EMAIL PROTECTED] Subject: [SQL] is it possible to get the number of rows of a table? I would like to compare the number of rows of one table and of another and use it in a query like this: SELECT * FROM WHERE number of rows of table EQUALS number of rows of table i.e. I only want get a query result if the tables have the same number of rows. Is there a function or a way to do this ? Thanks, Frederick __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Which SQL query makes it possible to optain the 3 greatest values of an interger list ?
I would use
SELECT id FROM table ORDER BY id LIMIT 0, 3;
in order to get the top 3 results. The key is Limit keyword.
==
Wei Weng
Network Software Engineer
KenCast Inc.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Yoann
Sent: Wednesday, September 12, 2001 4:41 AM
To: [EMAIL PROTECTED]
Subject: [SQL] Which SQL query makes it possible to optain the 3
greatest values of an interger list ?
OO( Sql Problem )Oo.
That is to say a table of 5 inputs of 1 integer field :
Table = { (1); (12); (3); (9); (4) }
We want to obtain a result of 1 input of 3 fields,
corresponding to the 3 greatest values of Table,
by descending order :
Result = { (12; 9; 4) }
= Which SQL query makes it possible to obtain Result from Table ?
We certainly need to use sub-queries, but how ?
Thank you in advance for your help !
Yoann AUBINEAU
---(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 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] PL/PGSQL and external (flat ASCII) files - Urgent ... :)
You can probably try to make a c extension that does open/close. On 17 Jul 2001 15:28:36 -0500, Chris Ruprecht wrote: Hi all, I need to know how I can access a flat file from within a PL/PGSQL script. I have an Oracle PL/SQL book here and they refer to FOPEN(...), FCLOSE(...) but these functions don't seem to exist in PL/PGSQL.. What can I do instead? I checked the Programmer's Guide to Procedural Languages (PostGreSQL 7.1.2 and 7.2) but there is no info on it. Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Turn off flushing after each write
How can I control that? Where is the setting I can tweak? I checked the doc at http://www.archonet.com/pgdocs/tweak-perf.html. Couldn't find any reference to it. Thanks! -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] shared memory size
Will increasing kernel shared memory size (in linux by doing echo 134217728 /proc/sys/kernel/shmall; echo 134217728 /proc/sys/kernel/shmmax) help with the speed of a complicated query with a large return set? (average 2 or more entries in return) Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(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] Is function atomic?
Does that mean if I used DECLARE ... BEGIN DO_STUFF END; the DO_STUFF will not be interrupted (maintain atomicity) even when multiple threads use the function concurrently? On 06 Jul 2001 18:13:37 +0200, Peter Eisentraut wrote: Wei Weng writes: If it is not, is it possible to acquire a lock on a row ? how about a lock on a table? SQL statements issued by server-side functions operate with the same transaction isolation semantics as normal SQL statements. However, whatever the function does outside the database may obviously have different semantics. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] Is function atomic?
What I wanted is simple: I don't want other processes that run the function at the same time have read/write access from the tables this function accesses(select and insert/update). Is it possible, say, let other proccess blocked on this function? On 06 Jul 2001 18:49:49 +0200, Peter Eisentraut wrote: Wei Weng writes: Does that mean if I used DECLARE ... BEGIN DO_STUFF END; the DO_STUFF will not be interrupted (maintain atomicity) even when multiple threads use the function concurrently? Interruption, atomicity, and concurrency are separate issues. The function could of course be interrupted if there's an error. (That error may be related to concurrency, such as a serialization failure.) The database interactions of the function will be atomic in the sense that rollback will work. Concurrent execution of a function is permitted, but there may be issues if you modify global state or there is a serialization failure. These are the same issues that you have to deal with in any programming environment. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SQL] Null set
This is a function I am working on: CREATE FUNCTION authenticate(int4) RETURN boolean AS 'SELECT * INTO tmp FROM user WHERE user_id = id; if SET_IS_NULL(tmp) return false; else return true;' LANGUAGE 'sql'; My question is: Is there a way to implement the SET_IS_NULL function in pl/pgsql? -- Wei Weng Network Software Engineer KenCast Inc. ---(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] How do I print a message in a function?
I did something like create function test() returns integer as ' declare msg varchar; begin msg := test; RAISE NOTICE message is || msg; end' language 'plpgsql'; The function compiled without any error, however, when I tried to run it as select test(); it broke down with an error message Unterminated Can anyone help me on this? Thanks! -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Subquery error. Help please!!
What version of postgresql are you using?
On 27 Jun 2001 17:09:14 -0400, kakerjak wrote:
Hey all..
Here's the table definition.
CREATE TABLE laboratory (
id int4 NOT NULL,
subid int2 NOT NULL,
name varchar(30) NOT NULL,
CONSTRAINT laboratory_pkey PRIMARY KEY (id, subid))
The way this table works is that each lab has one ID. If any information is
changed(there are actually more fields then what i showed, but they don't
affect the problem) then the ID remains the same, but the SUBID gets
incremented by 1. Thus, other tables linking to it just need to know the ID
and then read the ID with the biggest SUBID to get the most recent record.
Now, what I want to do is this. Create a list of all the laboratories using
only the most recent record for each (the biggest SUBID for every unique
ID).
Here's my select statement.
SELECT b.id, b.subid, b.name
FROM (SELECT c.id, MAX(c.subid) AS subid FROM laboratory AS c GROUP BY
id) AS a
INNER JOIN
laboratory AS b
USING id, subid
The subquery works on it's own and returns the desired ID, SUBID
combinations.
But when put into the other query I get parser errors.
If the subquery is placed before the JOIN, like it is above, then the error
i get says 'parse error at or near select'
If i flip the subquery around with the laboratory table then i get 'parse
error at or near ('
According to the documention online, it seems as if this statement should
work.
( http://www.postgresql.org/idocs/index.php?queries.html#QUERIES-FROM )
Any help would be appreciated. TIA
kakerjak
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
--
Wei Weng
Network Software Engineer
KenCast Inc.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] control structure in a transaction block?
I am using postgresql 7.1.2.
I am trying to implement the following pseudo code:
BEGIN
aid = select id from table_a where name = 'test';
if(aid != NULL)
then
{
update set name = 'test_test' where id = aid;
}
else
{
insert into table_a values (nextval('table_a_id_seq'), 'test');
}
COMMIT
But as we know, you can't use control structure in a transaction block.
And I can't use a function either, because in the real application,
there are way too many parameters needed to be passed in order to do the
insert or update.
Is there any tricks I can play here?
Thanks.
--
Wei Weng
Network Software Engineer
KenCast Inc.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Is this possible?
I have a table that has a serial for primary key. Is it possible to get the new available primary key right after I insert a row of new entry? Thanks Wei ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] simulate union in subselect
I know this is not do-able, but is there any way to simulate the following in Postgresql 7.1? select id from (select id, recv_date as date from table1 union select id, send_date as date from table2) AS subtable order by date; Thanks a lot Wei ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Sorting and then...
Suppose I have a table create table test ( id integer, name text ); And I want to get the names of the largest 10 "id"s. How can I do that in sql? Thanks! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
