Re: [GENERAL] How restrict select on a view ?
Andreas wrote: I'd like to have a view only to be used by certain users. The tables are public. Can this only be done by restricting access to the tables? GRANT/REVOKE works on views revoke all on aview from public; grant select on aview to user1; As Raymond pointed out, if user2 knows what the definition of aview is, they can just run it against the raw tables. e.g. create view aview as select * from pg_proc; revoke all on aview from public; grant select on aview to user1; set session authorization user2; select * from aview; -- fails select * from pg_proc; -- works and gives the same result klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: kgo...@une.edu.au -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Want quit milis
Tonny Sapri wrote: Iwant quit this milis. could you help me? I forget my milis password. go to this web site http://www.postgresql.org/mailpref/pgsql-general click Sign In at the top left. find the section that says Lost Password. put your email address in there and click the Mail My Password button. check your email for your list password. go back to the web site and login properly tick the check box next to pgsql-general click the unsubscribe button HTH, klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems With Bad PID and Missing Socket -- FIXED
Rich Shepard wrote: On Mon, 8 Dec 2008, Rich Shepard wrote: I had this happen several years ago, but do not recall what fixed the bad PID file and socket. The thread had been saved here, but I must have inadvertenly deleted it. Figured out how to fix the problem, but still cannot get SQL-Ledger to load. Time to ask on that mail list. How did you fix it? (so its filed in the archive) klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Favorite Tom Lane quotes
Scott Marlowe wrote: On Mon, Dec 1, 2008 at 10:42 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: On Tue, 2008-12-02 at 00:37 -0500, Greg Smith wrote: Tom on things that might ruin his tape backups: Then of course there are the *other* risks, such as the place burning to the ground, or getting drowned by a break in the city reservoir that's a couple hundred yards up the hill...Or maybe being burgled by Oracle employees who are specifically after my backup tapes. What is a tape? Apparently something to do with backups. But I don't know what those are either... :-P Never underestimate the bandwidth of a station wagon full of tapes hurtling down the highway. —Tanenbaum, Andrew S. (1996). Computer Networks. New Jersey: Prentice-Hall, 83. ISBN 0-13-349945-6. A modern chrysler town and country(1) has a cargo capacity of 140.1 cubic feet(2) letting it carry 17163 LTO4 (3) tapes at 800GB each. Thats 13730TB. Say it has to get from San Francisco to LA (about 6 hrs according to google maps directions(4)), that gives 2288TB/hour, or 5.1 terabit/second. klint. 1. its more of a minivan than a station wagon these days but close enough. halve the number of tapes if you're thinking of the classic woodie http://www.allpar.com/old/townandcountry.html 2. http://www.chrysler.com/hostc/vsmc/vehicleSpecModels.do?modelYearCode=CUC200908 3. dimensions from http://en.wikipedia.org/wiki/Linear_Tape-Open 4. http://maps.google.com/maps?f=dsaddr=san+franciscodaddr=los+angeleshl=engeocode=mra=lssll=37.0625,-95.677068sspn=58.598104,116.542969ie=UTF8ll=35.939855,-120.330885spn=7.601811,14.567871z=7 -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Group by clause creating ERROR: wrong record type supplied in RETURN NEXT (version 8.1.11 -- grr...)
Webb Sprague wrote:
select * from mkn.query_table_data ('select sum(p087001) as pop
from datatable_00040 group by substr(geo_id, 13, 6) order by
pop desc limit 10')
as FOO (pop integer);
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function query_table_data line 15 at return next
sum(int) returns bigint
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Seek within Large Object, within PL/* function?
Adam Seering wrote: Hi, I'm new here, apologies if this is the wrong list... I'm playing around with storing custom preprocessed data structures within Large Objects. I'd like to be able to write a custom function that will, within a query, let me select out particular bytestrings from the middle of a Large Object (within C, I think 'lo_lseek' and 'lo_read' would get me what I want). I'd like to do so from some PL/* embedded language, rather than a compiled C module. I can't find any documentation on doing this, though. Is it possible? Thoughts? According to http://www.postgresql.org/docs/8.3/static/lo-funcs.html , the functions are server side too postgres=# select proname from pg_proc where proname like 'lo%'; proname - lo_close lo_creat lo_create lo_export lo_import lo_lseek lo_open lo_tell lo_truncate lo_unlink log log log loread lower lowrite It's odd that loread and lowrite don't follow the naming convention with the underscore (8.3.3 win32)? klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] exception
hendra kusuma wrote: but I really like to ask if there is a way to catch any exeption instead of defining what exception we would like to catch? something like ELSE or what :) From the manual The special condition name |OTHERS| matches every error type except |QUERY_CANCELED|. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Date data type
Mike Hall wrote:
Gday,
I'm currently converting an MS Access database to PostgreSQL (version 8.1 as it
is the vesion that ships with CentOS 5).
I'm having trouble with an INSERT statement attempting to insert an empty value
('') into a field with data type DATE. This produces the following error
message:
ERROR: invalid input syntax for type date:
It does the same whether the field has a NOT NULL restraint or not.
I can't find anything relevant in the documentation.
What am I doing wrong.
You're trying to put the empty string into a date field
insert into atable (date_col) values (''); -- syntax error
insert into atable (date_col) values (null); -- works
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] valid use of wildcard
Scott Marlowe wrote: On Wed, Oct 29, 2008 at 5:04 PM, Irene Barg [EMAIL PROTECTED] wrote: Hi, Is the following query a valid use of the 'wildcard' in (='2008-10-27%')? [EMAIL PROTECTED] arcsoft]$ psql metadata Password: Welcome to psql 8.1.9, the PostgreSQL interactive terminal. metadata=# SELECT * FROM viewspace.siap AS t WHERE t.startDate='2008-10-27%' AND t.prop_id LIKE '%' LIMIT 1000; Causes the %CPU to jump and process lingers for over an hour. Bad move. dates aren't strings, and their format can change based on what you've got set for datestyle. If you want a start date (that's a date or a timestamp) then use the proper operators where startDate='2008-10-27' If startDate is a text / varchar type then you need to change it to a date. storing dates in strings is bad. Surprisingly, '2008-10-27%' casts to a date in 8.3.3. I was expecting the planner to cast the field to string to compare it (or throw an error about implicit casting), but the literal goes to the field type (see explain on a timestamp field below). Does the % have any special meaning in casts to date/timestamp? postgres=# select version(); version - PostgreSQL 8.3.3, compiled by Visual C++ build 1400 (1 row) postgres=# select '2008-10-27%'::date; date 2008-10-27 (1 row) postgres=# explain select * from data where timestamp = '2008-10-27%'; QUERY PLAN -- Seq Scan on data (cost=0.00..504.68 rows=2 width=27) Filter: (timestamp = '2008-10-27 00:00:00'::timestamp without time zone) (2 rows) postgres=# klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Username and Password authentication using pqxx library.
aravind chandu wrote:
I am using pqxx API for postgresql.The following is the
part of my code.The problem is in the query I am selecting the number
of rows from the table since there is only one user name and password
row it should return 1 and if there is no match then it should return
0.But here Even though I give wrong username or password its getting
authenticated.please help me,what should I put in the if ()
statement inorder to solve this issue .
result R(T.exec(select count(*) from dbtable where
username=+username+ and password=+password+));
if (R.empty())
{
throw logic_error(No tables found);
*shm = '5';
}
else
{
coutAuthenticated;
*shm='0';
}
You don't want to test if the result is empty - you want to test if the
value returned is 0.
If you select the username and group by it, then you could test for the
empty result.
# select count(*) from users where username = 'hfkhsdf';
count
---
0
(1 row)
# select username, count(*) from users where username = 'hfkhsdf' group
by 1;
username | count
--+---
(0 rows)
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] again... (win32 logging errors)
Ati Rosselet wrote: I'm still getting a lot of these entries in my eventlog whenever I have a reasonably large amount of logging: Event Type:Error Event Source:PostgreSQL Event Category:None Event ID:0 Date:10/22/2008 Time:9:36:28 AM User:N/A Computer:-- Description: could not write to log file: Bad file descriptor production server 8.3.3 (tried with 8.3.4 - same thing), win 2003 server. I'd send this to psql-bugs, but I can't figure out how to reproduce it.. and apparently noone else has seen this? Or is noone else running postgres on win2003??? (survey says??) The timing of each appears to be when log is rolled over (size limited to 10MB) and postgres attempts to write a log entry at the same time: Any ideas? help? How to proceed??? Change the log settings to panic and up the size limit. Almost nothing gets written to the log and the log never gets full enough to rotate. Other than that, you'll need to get visual studio and the source and work it out. Reproducing it is relatively easy - make a function that does raise notice and call it a lot. I had one that essentially took out the server. http://archives.postgresql.org/pgsql-general/2008-08/msg00615.php +1 for survey. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] feature idea
hubert depesz lubaczewski wrote: hi would it be possible to allow (in psql) syntax like: \do = | grep ... or even: select * from table; | zcat - /tmp/table.data.gz i.e. - adding | ... at the end of command to send its output via pipe to another program? usecase that i had today was pretty simple - check list of operators that are defined using password datatype. generally - i could: psql -c '\do' | grep -i password but simple way of doing it from shell would be so much nicer. we gave this feature already with \do - but i think it would be cool to be able to do it on statement level - in more natural (to unix veterans) way. what do you think? Maybe pgbash is what you are looking for. There were recent posts about it that can get to its home page. Looks like it will need some work to bring it up to current. http://archives.postgresql.org/pgsql-general/2008-09/msg01234.php klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I save data and then raise an exception?
Gurjeet Singh wrote: On Fri, Oct 3, 2008 at 7:14 AM, Alvaro Herrera [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Rob Richardson wrote: Here's what I need to do: IF query_check_fails THEN UPDATE some_table SET some_value = 0 WHERE some_condition_is_true; RAISE EXCEPTION 'Look, you idiot, do it right next time!'; END; I need the update to work, but I need to raise the exception so the C++ code recognizes the error. How can I do both? You need an autonomous transaction, which Postgres does not support directly but you can implement using dblink or a plperl function that connects back to the database. I was also going to suggest that but did not, because autonomous transaction won't help here! The data has been INSERTed or UPDATEd in this transaction, and hence won't be visible to the autonomous transaction, because the main transaction hasn't committed yet. Autonomous transactions in the oracle sense would do the job perfectly. http://www.oracle-base.com/articles/misc/AutonomousTransactions.php Faking that example with dblink isn't going to fly with PG - the select with 10 rows before the rollback is never going to see 10. For Rob's need though, running his update thru dblink it should do the job. If the data he's fixing with the update statement was in the same transaction, then the update wouldn't be needed at all and the whole thing could just rollback. You have to assume that by the point where Rob's code fires, the bad data is already committed. That update needs to commit to undo that previous transaction, but he still needs to get the 3rd party app to know that something went horribly wrong with its insert. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting unique rows as an aggregate.
Richard Broersma wrote: On Mon, Sep 29, 2008 at 4:36 PM, Tom Lane [EMAIL PROTECTED] wrote: SELECT count(distinct make), count(distinct color) from table WHERE criteria; Is this in the SQL spec? I didn't know Agg functions could do this? Yes. SQL92 6.5 set function specification ::= COUNT left paren asterisk right paren | general set function general set function ::= set function type left paren [ set quantifier ] value expression right paren set function type ::= AVG | MAX | MIN | SUM | COUNT set quantifier ::= DISTINCT | ALL I never realised that you could use it for more than count though. In 8.3.3, it worked for sum/avg/stddev/variance. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NULL values seem to short-circuit my unique index
Matthew Wilson wrote: I'm trying to comprehend how NULL values interact with unique indexes. It seems like I can insert two rows with NULL values in a column with a unique constraint just fine. Is there something special about NULL? Can anyone post some links to explain what is going on? http://www.postgresql.org/docs/8.3/interactive/ddl-constraints.html#AEN2058 Last paragraph just above 5.3.4. What's the deal with NULL? NULL = NULL is not true, it's null NULL NULL is not false, it's null It's the normal SQL 3 state logic (true/false/null) with only the true value from the comparison causing the constraint violation. Think of the unique constraint check like does this value equal any other value already recorded. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Largest PostgreSQL 8.x DB someone is running?
Keaton Adams wrote: What is the the largest PostgreSQL 8.x database that is running in a production environment that you are aware of? We top out at roughly 400 GB but have a need for a new project to go much, much larger (in the several TB range). I am attempting to get a feel for how large one should take a single PostgreSQL database, given all of the operational concerns such as overall performance with a thousand+ concurrent users, times/space requirements for backups and restores, how to upgrade to newer upcoming versions of the software, etc. especially since there are no parallel operations/features in the product. Any information you can provide would be very helpful. See this thread from last month http://archives.postgresql.org/pgsql-general/2008-08/msg00553.php klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger function is not called
Bill wrote:
The thing that has me confused is that the following table, trigger
and trigger function work perfectly and the primary key for this table
is also bigint not null. I added a bigint not null domain to this
schema and changed the data type of the key to the domain and then I
get the constraint violation. I changed the type of the key column
back to bigint not null and the trigger fires and no error occurs.
Perhaps explain verbose on the insert will make things clearer. When
the domain is used, there's a COERCETODOMAIN step that gets the constant
into the domain type. With the not null definition in the domain, this
blows up before anything else has a chance.
begin;
create schema test;
create sequence test.id_seq;
create domain mydom as bigint not null;
CREATE TABLE test.trigger_test
(
key bigint NOT NULL,
data character varying(16),
CONSTRAINT trigger_test_key PRIMARY KEY (key)
);
CREATE TABLE test.trigger_test2
(
key mydom,
data character varying(16),
CONSTRAINT trigger_test_key2 PRIMARY KEY (key)
);
CREATE OR REPLACE FUNCTION test.trigger_test_before_insert()
RETURNS trigger AS
$BODY$
begin
raise notice '*Test before insert*';
new.key := nextval('test.id_seq');
return new;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER trigger_test_insert
BEFORE INSERT
ON test.trigger_test
FOR EACH ROW
EXECUTE PROCEDURE test.trigger_test_before_insert();
CREATE TRIGGER trigger_test_insert2
BEFORE INSERT
ON test.trigger_test2
FOR EACH ROW
EXECUTE PROCEDURE test.trigger_test_before_insert();
explain verbose insert into test.trigger_test values (null,'hi');
--explain verbose insert into test.trigger_test2 values (null,'hi');
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fwd: How do I determine my data dir for a created database for pg_ctl?
Matthew Pettis wrote: I have a database I can psql into... How can I determine what its absolute path is so I can use pg_ctl on it to restart it? Because when I use pg_ctl, it tells me I have to provide it in the -D flag as an argument. show data_directory; klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.3.3 win32 crashing
Does this mean anything to anyone? Faulting application postgres.exe, version 8.3.3.8160, faulting module msvcr80.dll, version 8.0.50727.1433, fault address 0x0001e44a. I have a function that's doing a summary report counting data. It makes a couple of small temp tables then makes a big temp table. In the query that makes the big temp table, there's an immutable function that gets called thousands of times in the execution. In the immutable function, there was a raise notice for debugging . Without the raise, the query finishes in about 40sec. With it, the postgres.exe grinds down to about 2% cpu usage and eventually throws the message above. Then I reboot the machine - nothing responds to control. After reading of recent win32 network buffer problems, I'm wondering if this is another one. The context statement is nearly 8k long. The server is w2k3 on a 3.8ghz P4 with 3g memory on a 10mbit network (don't ask about the network - it just is and there's nothing I can do about it). The client is xp pentium M notebook, 2ghz, 1g memory. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.3 win32 crashing
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: Faulting application postgres.exe, version 8.3.3.8160, faulting module msvcr80.dll, version 8.0.50727.1433, fault address 0x0001e44a. I have a function that's doing a summary report counting data. It makes a couple of small temp tables then makes a big temp table. In the query that makes the big temp table, there's an immutable function that gets called thousands of times in the execution. In the immutable function, there was a raise notice for debugging . Without the raise, the query finishes in about 40sec. With it, the postgres.exe grinds down to about 2% cpu usage and eventually throws the message above. Then I reboot the machine - nothing responds to control. Please try to narrow it down a little. It seems like this could be caused by sending the messages to the postmaster log, or by sending them to the client, or by the client not processing them nicely. (You didn't say what client program you're using.) I'd suggest adjusting client_min_messages and log_min_messages so that the notice message goes to only one of the two places, and and then seeing what happens. Client is pgAdmin from the 8.3.3 installer. client=warning, log=warning completes client=warning, log=notice fails client=notice, log=warning completes client=notice, log=notice fails It fails a heck of a lot quicker with client=warning, log=notice. This is the raise that causes it CREATE OR REPLACE FUNCTION stagecode(date, date) RETURNS text AS $BODY$ declare TimeSpan integer = $2 - $1; begin raise notice '%',TimeSpan; return case when $1 is null or $2 is null then 'X' when TimeSpan 10 then 'B' when TimeSpan 70 then 'L' when TimeSpan 120 then 'W' when TimeSpan 330 then 'P' when Timespan 450 then 'Y' when Timespan 700 then 'H' else 'A' end; end;$BODY$ klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.3 win32 crashing
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: [ heavy RAISE NOTICE traffic crashes the server ] client=warning, log=warning completes client=warning, log=notice fails client=notice, log=warning completes client=notice, log=notice fails It fails a heck of a lot quicker with client=warning, log=notice. Okay, so the problem is definitely on the postmaster-log side. Please show us all of your logging-related configuration settings. I can duplicate it on my notebook with a slightly smaller set of data (pentium M, 2ghz, 1g mem, 5400rpm pata drive 23gig free). Both server and notebook were installed off the same installer. All settings not commented. The log_min_messages is only there from the test runs. It's normally the default. port = 5432# (change requires restart) max_connections = 100# (change requires restart) shared_buffers = 32MB# min 128kB or max_connections*16kB max_fsm_pages = 204800# min max_fsm_relations*16, 6 bytes each log_destination = 'stderr'# Valid values are combinations of logging_collector = on# Enable capturing of stderr and csvlog log_min_messages = warning log_line_prefix = '%t '# special values: datestyle = 'iso, mdy' lc_messages = 'C'# locale for system error message lc_monetary = 'C'# locale for monetary formatting lc_numeric = 'C'# locale for number formatting lc_time = 'C'# locale for time formatting default_text_search_config = 'pg_catalog.english' The notebook as visual studio 2005 professional if that helps but it's not setup to build postgres. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] can't get UPDATE ... RETURNING ... INTO ... to compile successfully
Dale Harris wrote:
It works for the INSERT command, but not UPDATE. For the INSERT command, it
makes my code look neater and I image it's more efficient too.
This time I am trying to UPDATE a field using a primary key, and return
another field into a variable so that I can take necessary action if
required later in the plpgsql script. I know that I can issue another
SELECT query to retrieve the information, but I would have thought it would
be a lot more efficient to return the value during the UPDATE.
Works for me
test=# begin;
BEGIN
test=#
test=# create table foo(f1 int, f2 text);
CREATE TABLE
test=# insert into foo values(1, 'hi');
INSERT 0 1
test=# insert into foo values(2, 'hello');
INSERT 0 1
test=#
test=# create function bar(int,int) returns boolean as $$
test$# declare
test$#r record;
test$# begin
test$#update foo set f1 = $2 where f1 = $1 returning * into r;
test$#raise notice '% %',r.f1,r.f2;
test$#return true;
test$# end;$$ language plpgsql volatile;
CREATE FUNCTION
test=#
test=# create function bar1(text) returns boolean as $$
test$# declare
test$# r record;
test$# begin
test$# for r in
test$#update foo set f2 = f2 || $1 returning *
test$# loop
test$#raise notice '% %',r.f1,r.f2;
test$# end loop;
test$# return true;
test$# end;
test$# $$ language plpgsql volatile;
CREATE FUNCTION
test=#
test=# select * from bar(2,3);
NOTICE: 3 hello
bar
-
t
(1 row)
test=#
test=# select * from bar1('!');
NOTICE: 1 hi!
NOTICE: 3 hello!
bar1
--
t
(1 row)
test=#
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] can't get UPDATE ... RETURNING ... INTO ... to compile successfully
Dale Harris wrote: As per the original message: I've got some code which postgres 8.3.3 won't accept. Postgres doesn't like the INTO clause on RETURNING INTO and I've tried following the documentation. UPDATE EntityRelation SET Status = inStatus, Modified = Session_TimeStamp(), ModifiedBy = UserID() WHERE (RelationID = inRelationID) AND (EntityID = inEnityID) AND IsEqual(inRelatedID, RelatedID) RETURNING Default INTO oldDefault; Does anyone have any ideas if the INTO clause actually works at all for an UPDATE statement? And documentation link which advises that the UPDATE statement should be able to *return a value into a variable* in plpgsql. http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html The query above is out of my plpgsql script and the WHERE clause selects an unique record. Therefore only 1 value should ever be returned. The point is that I don’t even get that far as the script fails to compile due to the INTO clause. Show us the whole function - then we can try it and see where the problem is. What is the actual message you get? Are you sure you spelled entity right in inEnityID (need another T perhaps)? Does select version() actually say 8.3.3? The statement you posted works for me. test=# begin; BEGIN test=# test=# create table EntityRelation test-# (EntityID int, test(# Status int, test(# Modified timestamp, test(# ModifiedBy text, test(# RelationID int, test(# RelatedID int, test(# Default text); CREATE TABLE test=# test=# insert into EntityRelation test-# values (1,1,now(), 'me', 1,1,'hello'); INSERT 0 1 test=# test=# create or replace function foo() returns boolean as $$ test$# declare test$# oldDefault text; test$# instatus int = 1; test$# inRelationID int = 1; test$# inRelatedID int = 1; test$# inEnityID int = 1; test$# test$# begin test$# test$# UPDATE EntityRelation test$# SET Status = inStatus, test$# Modified = now(), test$# ModifiedBy =current_user test$# WHERE (RelationID = inRelationID) test$# AND (EntityID = inEnityID) test$# AND inRelatedID = RelatedID test$# RETURNING Default test$# INTO oldDefault; test$# test$# raise notice '%', oldDefault; test$# return false; test$# test$# end;$$ language plpgsql volatile; CREATE FUNCTION test=# test=# select foo(); NOTICE: hello foo - f (1 row) test=# klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help returning record set from a dynamic sql query
[I'm not going to even try to work out that mess to quote it] The following works for me. You can even do it without dynamic sql (see fun_orderreport1). begin; -- dummy up some tables for self contained example create table orders (ordersid int, initiated date, company int, event int); create table company (companyid int, companyname text); create table event (eventid int, company int, eventname text); -- dummy up some data insert into company values (1,'COMPANY'); insert into orders values (1, current_date, 1, 1); insert into event values(1, 1, 'EVENT'); -- make the function CREATE OR REPLACE FUNCTION fun_orderreport(pmorderid integer, pmcompanyid integer, pmeventid integer) RETURNS SETOF record AS $BODY$ DECLARE vResult record; vSql TEXT = ' SELECT ORDR.ORDERSIDAS OrderID, ORDR.INITIATED AS Order_Date, COMP.COMPANYNAME AS Company_Name, EVNT.EVENTNAME AS Event_Name FROM ORDERS ORDR INNER JOIN COMPANY COMP ON COMP.COMPANYID = ORDR.COMPANY INNER JOIN EVENT EVNT ON EVNT.COMPANY = COMP.COMPANYID WHERE ORDR.EVENT = EVNT.EVENTID '; BEGIN IF $1 IS NOT NULL THEN vSql = vSql ||' AND ORDR.ORDERSID = '|| $1; END IF; IF $2 IS NOT NULL THEN vSql = vSql ||' AND COMP.COMPANYID = '|| $2; END IF; IF $3 IS NOT NULL THEN vSql = vSql ||' AND EVNT.EVENTID = '|| $3; END IF; FOR vResult IN EXECUTE vSql LOOP RETURN NEXT vResult; END LOOP; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; -- run it create temp table foo on commit drop as SELECT 1,* from fun_orderreport(NULL,NULL,NULL) a (orderid int, order_date date, company_name text, event_name text) union SELECT 2,* from fun_orderreport(1,NULL,NULL) a (orderid int, order_date date, company_name text, event_name text) union SELECT 3,* from fun_orderreport(NULL,1,NULL) a (orderid int, order_date date, company_name text, event_name text) union SELECT 4,* from fun_orderreport(NULL,NULL,1) a (orderid int, order_date date, company_name text, event_name text) union SELECT 5,* from fun_orderreport(2,NULL,NULL) a (orderid int, order_date date, company_name text, event_name text) union SELECT 6,* from fun_orderreport(1,1,1) a (orderid int, order_date date, company_name text, event_name text); -- do the same thing without execute CREATE OR REPLACE FUNCTION fun_orderreport1(pmorderid integer, pmcompanyid integer, pmeventid integer) RETURNS SETOF record AS $BODY$ DECLARE vResult record; begin for vResult in SELECT ORDR.ORDERSIDAS OrderID, ORDR.INITIATED AS Order_Date, COMP.COMPANYNAME AS Company_Name, EVNT.EVENTNAME AS Event_Name FROM ORDERS ORDR INNER JOIN COMPANY COMP ON COMP.COMPANYID = ORDR.COMPANY INNER JOIN EVENT EVNT ON EVNT.COMPANY = COMP.COMPANYID and evnt.eventid = ordr.event WHERE ordr.ordersid is not distinct from coalesce($1, ordr.ordersid) and comp.companyid is not distinct from coalesce($2, comp.companyid) and evnt.eventid is not distinct from coalesce($3, evnt.eventid) loop RETURN NEXT vResult; END LOOP; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; -- run it create temp table bar on commit drop as SELECT 1,* from fun_orderreport1(NULL,NULL,NULL) a (orderid int, order_date date, company_name text, event_name text) union SELECT 2,* from fun_orderreport1(1,NULL,NULL) a (orderid int, order_date date, company_name text, event_name text) union SELECT 3,* from fun_orderreport1(NULL,1,NULL) a (orderid int, order_date date, company_name text, event_name text) union SELECT 4,* from fun_orderreport1(NULL,NULL,1) a (orderid int, order_date date, company_name text, event_name text) union SELECT 5,* from fun_orderreport1(2,NULL,NULL) a (orderid int, order_date date, company_name text, event_name text) union SELECT 6,* from fun_orderreport1(1,1,1) a (orderid int, order_date date, company_name text, event_name text); -- if this returns any rows there's a difference (select * from foo except select * from bar) union (select * from bar except select * from foo); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can I search for text in a function?
Rob Richardson wrote: Sometimes I need to track down how something happens in the database our application relies on, but whatever's happening may be buried in some old function that everybody here has forgotten about long ago. IIRC, functions are stored internally merely as fields in a table owned by the system. Is there a query I can use to find what function contains the string previous_charge? select proname from pg_proc where prosrc ilike '%previous_charge%'; klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bytea encode performance issues
Alvaro Herrera wrote: Merlin Moncure escribió: er, I see the problem (single piece of text with multiple encodings inside) :-). ok, it's more complicated than I thought. still, you need to convert the email to utf8. There simply must be a way, otherwise your emails are not well defined. This is a client side problem...if you push it to the server in ascii, you can't use any server side text operations reliably. I think the solution is to get the encoding from the email header and the set the client_encoding to that. However, as soon as an email with an unsopported encoding comes by, you are stuck. Why not leave it as bytea? The postgres server has no encoding problems with storing whatever you want to throw at it, postgres client has no problem reading it back. It's then up to the imap/pop3/whatever client to deal with it. That's normally the way the email server world works. FWIW the RFC for email (822/2822) says it is all ASCII so it's not a problem at all as long as every email generator follows the IETF rules (body here is not just the text of the message - its the data after the blank line in the SMTP conversation until the CRLF.CRLF). 2.3. Body The body of a message is simply lines of US-ASCII characters. The 2 things that will make a difference to the query is 1. get rid of the encode call and 2. stop it being toasted Assuming that the dbmail code can't be changed yet 1. make encode a no-op. - create schema foo; - create function foo.encode (bytea,text) returns bytea as $$ select $1 $$ language sql immutable; - change postgresql.conf search_path to foo,pg_catalog, This completly breaks encode so if anything uses it properly then it's broken that. From the query we've seen, we don't know if it's needed or not. What query do you get if you search for something that has utf or other encoding non-ASCII characters? If it looks like the output of escape (i.e. client used PQescapeByteaConn on the search text), then the escape might be required. 2. dbmail already chunks email up into ~500k blocks. If that is a configurable setting, turn it down to about 1.5k blocks. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cursor
Christophe wrote: On Jul 29, 2008, at 2:35 PM, Tom Lane wrote: No, he does need an OPEN. Really? I thought that PG didn't use OPEN: The PostgreSQL server does not implement an OPEN statement for cursors; a cursor is considered to be open when it is declared. http://www.postgresql.org/docs/8.3/interactive/sql-declare.html It's different in PL/pgSQL. Before a cursor can be used to retrieve rows, it must be opened. (This is the equivalent action to the SQL command DECLARE CURSOR.) http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy ... from stdin csv; and bytea
David Wilson wrote: On Mon, Jul 28, 2008 at 1:24 AM, Klint Gore [EMAIL PROTECTED] wrote: Try just a single \ e.g. ge.xls,application/vnd.ms-excel,71168,\320\317\021\340\241[snip] Thanks- I did try that, and it at least gave the expected output from select, but is there a way to verify that it's actually handling it correctly rather than simply storing the sequence of characters? I'm not certain how to check the actual byte width of a column within a row, and I'd *really* rather not be storing 4 bytes for every 1 in the binary if I can avoid it- this column is already going to be doubling field width; quadrupling it would give me space headaches I really don't want to deal with. :) select length(bytea_field) from table You could use ||pg_relation_size|(|text|)| or ||pg_total_relation_size|(|text|) |to see how much disk space it takes up. You can play with the storage settings for the column if you want to try and handle the space better. see alter table set storage. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy ... from stdin csv; and bytea
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: David Wilson wrote: I'm not certain how to check the actual byte width of a column within a row, select length(bytea_field) from table If you want the actual on-disk footprint, use pg_column_size() Size on disk would have the compression from the default storage = extended wouldn't it? I verified it for myself manually anyway. copy (select * from original limit 5) to stdout with csv; create table foo (like original); alter table foo alter column bytea_field set storage external; copy foo from stdin with csv; select |reltoastrelid from pg_class where relanem = 'original' found the file for it and looked at it with a hex viewer. | klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] a SQL query question
Rajarshi Guha wrote: What I'm trying to do is to select those rows where pid is not null, grouped by pid. From within each group I'd like to select the row that has the maximum value of nmol. Distinct on should do the job for you. select distinct on (pid) aid, pid, nmol from atable where pid is not null order by pid, nmol desc If you want the rows tie for max nmol within a pid then you can go to select aid,pid,nmol from atable where (pid,nmol) in (select pid, max(nmol) from atable where pid is not null group by pid) klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] why can't I load pgxml.sql
Yi Zhao wrote: my version is 8.3.3: I found that it's part of the server. is it said that I should build postgresql with libxml again?? googling gzopen64 throws up a whole lot of hits - most of them were debian/ubuntu and boiled down to some problem with versions of libz.so klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy ... from stdin csv; and bytea
David Wilson wrote: My application is adding a bytea column to a table into which data is dumped in approximately 4k row batches, one batch approximately every 10 seconds. To this point, those dumps have used copy from stdin; however, I'm having some difficulty getting bytea encodings to work with it. Specifically, I can't seem to get the parser to recognize that what I'm handing it is an escaped string that needs to be parsed back into individual bytes rather than stored as-is. The encoding is very straightforward for INSERT, of course, but the COPY ... FROM STDIN CSV doesn't seem to want to work no matter what I've tried: \\000 \\000 E'\\000' etc. Is there a trick to this that I just didn't see in the documentation, or is this some limitation of CSV copy-in? If the latter, are there suggestions for workarounds other than to fallback on the inserts? Try just a single \ e.g. ge.xls,application/vnd.ms-excel,71168,\320\317\021\340\241[snip] klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERL DBI] Insertiing data across multiple tables
JD Wong wrote: Does anybody know how to insert data over multiple tables transactionally? The relationship is 1:1 with the latter table having a foreign key constraint. In order to add data to Table2 I need to know the primary key value of the same record in Table1 before it's committed, which in this case is a serial integer which of course is regulated by an implicit index. Have a look at INSERT ... RETURNING. It can feed back the new rows to your application so that you can make your 2nd insert without having to pre/reselect them (assuming your running 8.2+) klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Substitute a variable in PL/PGSQL.
Steve Martin wrote: I am trying to create a PL/PGSQL function to return the values of the fields in a record, e.g. 1 value per row in the output of the function. How do you substitute a variable? CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$ DECLARE ted varchar; bob RECORD; BEGIN FOR bob IN SELECT * FROM test LOOP FOR i IN 1..10 LOOP ted := 'bob.col' || i; RETURN NEXT ted; END LOOP; END LOOP; RETURN; END $$ LANGUAGE plpgsql; Or is there another way other than using another procedural language. Thanks - Steve M. There's no direct way to reference a particular field in a record variable where the field name is held in a variable in pl/pgsql. I.E. if ted = 'col1' there's no way to reference bob.ted to give you the value of bob.col1. If you want it easy to code but have to create something for every table and modify it ever time the table changes create view test_vertical_table as select col1::text from test union all select col2::text from test union all select col3::text from test union all select col4::text from test union all select col5::text from test ... If you want to go the generic function route CREATE OR REPLACE FUNCTION testfunc(text) RETURNS SETOF text AS $$ DECLARE vertTableName alias for $1; ted text; bob RECORD; bill record; BEGIN for bill in select table_name, column_name from information_schema.columns where table_schema = public and table_name = vertTableName loop FOR bob IN execute 'SELECT '||bill.column_name||' as thiscol FROM '||bill.table_name LOOP ted := bob.thiscol; RETURN NEXT ted; END LOOP; end loop; RETURN; END $$ LANGUAGE plpgsql; klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] query optimization
Kevin Duffy wrote:
So here are the questions for the PSQL gurus:
Is getsectypekey(‘CFD’) executing for every join (or possible join)
between positions_gsco and security?
Causing a scan of security for every possible join.
Does ‘ (select getsectypekey('CFD') ) ‘ cause the getsectype()
function to be executed once and thus
allowing the index on security to be used.
And of couse ‘5’ makes things simple. The index on security is used.
Am I posting this in the right. If not please help me correct my error
and point me to the correct spot.
Is the function stable or volatile?
As Scott Marlowe suggested, you need to look at the explain results to
find out what the plan is in each case.
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Referential integrity vulnerability in 8.3.3
Sergey Konoplev wrote: CREATE OR REPLACE FUNCTION fktrigfn() RETURNS TRIGGER AS $$ BEGIN PERFORM 1 FROM table1 WHERE a = OLD.aref; IF FOUND THEN RAISE NOTICE 'aborting delete for %', OLD.aref; RETURN NULL; ELSE RAISE NOTICE 'allowing delete for %', OLD.aref; RETURN OLD; END IF; END; $$ LANGUAGE plpgsql; That should be OK, because the row should always be marked as removed from table1 before the delete cascades. Well, your solution doesn't solve the main problem that sounds like Table2 contains rows with FK fields refer to deleted rows from table1 when ON DELETE action of the FKs is CASCADE. The only additional thing fktrigfn() does is informing about zombie rows appearance in logs. It does work around the problem. The perform line sets found to true if the row exists in the referred table and returns the NULL to prevent the delete without crashing the transaction. If it doesn't find the row in the referred table, then it assumes it must be in a foreign key cascading delete and returns OLD so that the rest of the delete happens. i.e. the sequence of events is 1. statement delete from table1 where pk=blah 2. the row is removed from table1 3. attempt delete on table2 4. fktrigfn fires 5. found is set to false by the perform 6. old is returned 7. the row is removed from table2 as opposed to 1. statement delete from table2 where pk=foo 2. fktrigfn fires 3. found is set to true by the perform 4. null is returned 5. nothing changes You would need to work the same logic into where you return null in your real trigger. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cause of error message?
Bayless Kirtley wrote: An old app is giving a new error message. I query a table and make one update. Later I come back to query the table again and get this error: ERROR: current transaction is aborted, commands ignored until end of transaction block The error only occurs after I have made an update. As long as no updates, I can query multiple times. Can anyone give me an idea of what sort of actions or errors usually cause this error? It means that you have an open transaction and the statement before you get this message failed. e.g. postgres=# begin; BEGIN postgres=# update bob set abc=1; ERROR: relation bob does not exist postgres=# select version(); ERROR: current transaction is aborted, commands ignored until end of transaction block postgres=# You need to find which statement failed first and fix it or rollback and have your app deal with the failure. The postgres log is a good place to start looking. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Out of memry with large result set
[EMAIL PROTECTED] wrote: I am doing a query via psql on a huge database, and I want to have its output piped to other unix processes. As the result set is very big, I've got: out of memory for query result. How can I manage that, without playing with cursors, as I do not want to change the sql query ? Under mysql, I have the same issue, but by using: mysql -quick, I have what I want. Is there something equivalent under postgresql ? Or should I use another clients ? Try copy (query) to stdout. For me, psql sits at 4.9mb ram on a 3x10^16 row query. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
download latest points to wrong place [was Re: [GENERAL] Installing PostgreSQL without using CygWin]
Dann Corbit wrote: The current version of the Installer installs a native build. Cygwin has not been used in the standard Windows build for a long time. Go here: http://www.postgresql.org/ftp/binary/v8.3.1/win32/ Get this: http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fbinary%2Fv8.3.1%2Fwin32%2Fpostgresql-8.3.1-1.zip The 'latest' directory on http://www.postgresql.org/ftp/binary/ points to 8.3.1 not 8.3.3 This is the one you need http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fbinary%2Fv8.3.3%2Fwin32%2Fpostgresql-8.3.3-1.zip klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restoring Database from Data directory backup.
J Ottery wrote: Using XP Pro and Postgres 8.3 I made a backup of the Data directory of the Postgres Installation to another place on the hard disk. Now the customer has uninstalled postgres then reinstalled a new one. Of course the original tables are all gone. Is there a method of retrieving the original tables and data from the backedup Data directory. If you can get the same version that they used to have you, should be able to start it on the same platform. Look for the PG_VERSION file in the data directory. Download from http://www.postgresql.org/ftp/binary/ I'm assuming that your backup took the entire directory tree under data and postgres wasn't running when you did it? klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Top N within groups?
Martijn van Oosterhout wrote: On Fri, Jul 11, 2008 at 01:24:28PM +1000, Klint Gore wrote: [thinking out loud] Can someone familiar with the source for DISTINCT ON comment on how hard it would be to add another parameter to return more than one row? From a programming point of view, it wouldn't be too hard. However, deciding what syntax to use is a another question entirely. I think your suggestion of FOR isn't good (ambiguous syntax, 'for' could be a column name), but I can't think of a better one. 'for' is a reserved key word so can't be a column name. http://www.postgresql.org/docs/8.3/interactive/sql-keywords-appendix.html postgres=# create table foo (for int); ERROR: syntax error at or near for The worst I think you could get would be select distinct on (for) for 4 for from table4 for update; but even then, I think the parser could work out what you want. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Top N within groups?
[was {SOLVED?] Re: [GENERAL] functional index not used, looping simpler
query just faster]
Ivan Sergio Borgonovo wrote:
I'm still curious to know if this could be done
efficiently with just one query.
[thinking out loud]
Can someone familiar with the source for DISTINCT ON comment on how hard
it would be to add another parameter to return more than one row?
e.g.
To do TOP 1 within an unknown number of groups
select distinct on (groupid) groupid, identifier, count(*)
from somequery
group by groupid, identifier
order by 1,3 desc,2;
I'm thinking that, for the top 3 within each group, something like
select distinct on (groupid) FOR 3 groupid, identifier, count(*)
from somequery
group by groupid, identifier
order by 1,3 desc,2;
For Ivan's case, groupid = brand, identifer = item. The where clause
applies the date limits.
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rollback
Adrian Moisey wrote: Hi I would like to be able to mark a point in my postgres database. After that I want to change a few things and rollback to that point. Does postgres support such a thing? Is it possible for me to do this? Well, transactions do that. If you want to do this inside a transaction, the term you're looking for is savepoints. I would like to do this globally over the entire DB, is that possible? Sure. You can start a transaction, create tables, drop tables, do inserts and deletes, update some tables, create and delete functions, triggers, views and schemas. After that, rollback. No problem. Can I do this outside of a transaction? How about create database a_copy template = original_database To recover at later date drop database original_database; alter database a_copy rename to original_database; There will be all sorts of issues so it might be impractical depending on your situation. - permission (have to be able to create/drop/rename databases) - concurrency (noone connected for the drop, everything done by anyone else will be forgotten as well) - time / capacity (creating a copy of a large database might take a while and need extra storage) klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SELECT Query returns empty
Bright D.L. wrote: Processes P1 and P2 are executables developed in VC++. These are the steps performed by P1 before sending the TCP packet (which acts as a trigger) to P2. 1) Create an insertion query 2) Execute the query 3) Execute a 'Commit' command 4) Repeat 2 and 3 how many ever times needed 5) 'Select' part of the last 'insert'ed data to verify whether it is accessible 6) Repeat 6 till the data is available 7) Send a TCP packet to P2 to start its 'Select' query Is there any chance that P2 is still in transaction from its last query? Once step 6 completes, can psql see the data? Does select * from pg_stat_activity show anything unexpected? Are you directly using libpq or some other connection method? klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SELECT Query returns empty
Bright D.L. wrote: Once step 6 completes, can psql see the data? Yes, P1 makes sure and is able to see the data before sending Packet to P2. Not P1, but psql. If you can see the data from psql, then your problem has to be in P2. If you can't see the data from psql, then P1 is the problem. Does select * from pg_stat_activity show anything unexpected? I have to investigate on that side. If it's as everyone thinks, then one of them will probably be idle in transaction when queried from psql between step 6 and 7. Running it from P1 or P2 will just tell you that its running select * from pg_stat_activity, not if it's still in transaction. You could try setting log_statement='all' and check that the logs look like you would expect. Especially the commits from both sides. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting source code for database objects
Artacus wrote: On pgadmin, when you click on a table or function, you get the source code (DDL) to create that table or function. I want to take that and check it into subversion so I have one file for each table, function, view, etc. My question is, how do you get that source code? I've been playing with pgadmin and wireshark trying to figure out what commands or queries it is using to no avail. See GetSql method of schema/*.cpp This used to be on the list of possible PG projects for the google summer of code but seems to have been removed. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump - lost synchronization with server: got message type d, length 6036499
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: Tom Lane wrote: Would you be willing to send me a pg_dump -s (ie, just schema no data) dump of this DB? Off-list of course. attached. created with pg_dump 8.3.3 win32 to the 8.3.1 win32 server. Thanks. When I reload this here and try to pg_dump it, I see no evidence of any problem --- pg_dump completes pretty quickly and seems to eat no more than a megabyte or so. I'm not sure what to make of that. Could there be a Windows-specific memory problem in pg_dump? Any more thoughts on this? The file being produced when dumping the table in question is about 1 gig when it crashes. In the last check-in for fe-protocol3.c, there's notes about 1 gig strings so I'm wondering if my data when received by pg_dump (or output by the 8.3.1 server) goes over? 270m of bytea converted to \xxx octal strings would make something 1 gig. But the 270m is spread over 400 rows with nothing bigger than 3m. If there's no interest in researching this any further, then I'll stop the server and take a file system copy of the data directory and upgrade it to 8.3.3 and pray the problem goes away. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump - lost synchronization with server: got message type d, length 6036499
Can someone shed some light on what's happening here? D:\backupspg_dump -Z 9 -Fc -C -U postgres -f sheepcrc\dbback.dmp sheepcrc2 pg_dump: Dumping the contents of table uploadeddatafiles failed: PQgetCopyData () failed. pg_dump: Error message from server: lost synchronization with server: got messag e type d, length 6036499 pg_dump: The command was: COPY public.uploadeddatafiles (id, username, projectid , aspsession, filename, filetype, filesize, filedata, uploadedon, timestamp) T O stdout; pg 8.3.1 server on w2k3 pg_dump 8.3.1 and 8.3.3 on the same machine both do the same thing. data directory and backup directory excluded from virus scanner. The followinng variations also failed. The length seems to be either 6036499 or 8435588 (84... is most common). pg_dump -Z 9 -Fp -C -U postgres -f sheepcrc\dbback.dmp sheepcrc2 pg_dump -Fp -C -U postgres -f sheepcrc\dbback.dmp sheepcrc2 uploadeddatafiles holds excel spreadsheets in the filedata column. sheepcrc2=# select count(*) from uploadeddatafiles; count --- 405 (1 row) sheepcrc2=# select sum(length(filedata)) from uploadeddatafiles; sum --- 271067619 (1 row) This completes and returns all rows select id, md5(filedata) from uploadeddatafiles klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump - lost synchronization with server: got message type d, length 6036499
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: Can someone shed some light on what's happening here? D:\backupspg_dump -Z 9 -Fc -C -U postgres -f sheepcrc\dbback.dmp sheepcrc2 pg_dump: Dumping the contents of table uploadeddatafiles failed: PQgetCopyData () failed. pg_dump: Error message from server: lost synchronization with server: got messag e type d, length 6036499 6 meg doesn't seem particularly enormous though. Are you running pg_dump under some especially restrictive user limits? Maybe it's dying here after having leaked a lot of memory for some other reason --- try watching the pg_dump process size while it runs. I'm running it under my own account which has adminstrator rights. Peak memory usage was about 540m which brought the total usage for the machine to about half the physical memory allocated (3g total). Is there a binary debug build for win32 somewhere? klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump - lost synchronization with server: got message type d, length 6036499
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: Tom Lane wrote: Maybe it's dying here after having leaked a lot of memory for some other reason --- try watching the pg_dump process size while it runs. Peak memory usage was about 540m which brought the total usage for the machine to about half the physical memory allocated (3g total). Well, that might well explain the failure. pg_dump does suck a lot of schema information into memory at startup, but 540m seems excessive. Maybe you've found a memory leak in pg_dump (it wouldn't be the first one). Does this database have a particularly large number of objects? regards, tom lane I wouldn't call it large - 27 tables, 111 functions, 21 custom types (used for set returning function results). The biggest row count table has about 200k records (structure is int,int,timestamp) The biggest physical table is the one thats failiing. The table itself is physically 81m and its toast table is 82m. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump - lost synchronization with server: got message type d, length 6036499
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: Tom Lane wrote: Maybe you've found a memory leak in pg_dump (it wouldn't be the first one). Does this database have a particularly large number of objects? I wouldn't call it large - 27 tables, 111 functions, 21 custom types (used for set returning function results). [ squint... ] Hard to see how that could be eating half a gig of pg_dump memory space. Would you be willing to send me a pg_dump -s (ie, just schema no data) dump of this DB? Off-list of course. Upon more investigation pg_dump 8.3.1 on linux (ubuntu feisty) across the network completes properly. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Date Formatting for dd/mm/yyyy
J Ottery wrote:
Hi all.
I am using Postgres 8.3 with Windows XP Pro.
System date format is dd/MM/
Having problems when I writing records to a database with a single
'date' type field.
e.g. FieldByName('Date').ASString:='2/2/2003'
What is delphi's ShortDateFormat set to?
Does fieldbyname('Date').asdatetime := strtodate('2/2/2003') make any
difference?
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dblink to non postgresql dbms
David Rowley wrote: Hello all, I’m looking for a solution to query a SQL Server 2000 instance from PostgreSQL 8.3.3. I’ve been trawling the internet for some type of solution with out any luck, I only found old references to someone talking about implementation of create database link to postgresql Does anyone know of anything? I’m trying to avoid having to write a C table returning function. Any help would be much appreciated Maybe these things from pgfoundary might help http://pgfoundry.org/projects/dbi-link/ http://pgfoundry.org/projects/odbclink/ http://pgfoundry.org/projects/dblink-tds/ -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LIKE not using indexes (due to locale issue?)
Ow Mun Heng wrote: On Wed, 2008-06-25 at 14:58 +1000, Klint Gore wrote: Ow Mun Heng wrote: explain select * from d_trr where revision like '^B2.%.SX' --where ast_revision = 'B2.M.SX' Seq Scan on d_trr (cost=0.00..2268460.98 rows=1 width=16) Filter: ((revision)::text ~~ '^B2.%.SX'::text) show lc_collate; en_US.UTF-8 Is it that this is handled by tsearch2? Or I need to do the locale to C for this to function? See http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html. It tells you how to create an index that like might use in non-C locales. Just more information. This columns is created with the varchar type. original index is created using CREATE INDEX idx_d_ast ON xmms.d_trh USING btree (revision varchar_pattern_ops); CREATE INDEX idx_d_ast2 ON xmms.d_trh USING btree (revision); after creating it, seems like it is still doing the seq_scan. So what gives? Can I get more clues here? Post your query and the explain analyze of it and how many rows are in the table. In my database, there's 7200 rows in items and I know that none of the identifiers for them start with 'xb'. As you can see below, the 1st query is sequential and the 2nd one is using the new index. (v8.3.0) postgres=# show lc_collate; lc_collate English_Australia.1252 (1 row) postgres=# explain analyse select * from items where identifier like 'xb%'; QUERY PLAN - Seq Scan on items (cost=0.00..160.18 rows=1 width=113) (actual time=4.966..4.966 rows=0 loops=1) Filter: ((identifier)::text ~~ 'xb%'::text) Total runtime: 5.029 ms (3 rows) postgres=# create index anindex on items(identifier varchar_pattern_ops); CREATE INDEX postgres=# explain analyse select * from items where identifier like 'xb%'; QUERY PLAN --- Index Scan using anindex on items (cost=0.00..8.27 rows=1 width=113) (actual time=0.165..0.165 rows=0 loops=1) Index Cond: (((identifier)::text ~=~ 'xb'::text) AND ((identifier)::text ~~ 'xc'::text)) Filter: ((identifier)::text ~~ 'xb%'::text) Total runtime: 0.255 ms (4 rows) -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LIKE not using indexes (due to locale issue?)
Ow Mun Heng wrote: Could it be that it's not able to determine the B2.%.SX in there? explain select count(*) from d_trr_iw where ast_revision like 'B2.P.SX' even this will result in a seq_scan. How many values have you got that start with 'B2.'? If it's more than about 5% then it's probably quicker to sequential scan anyway. Since the equals query says there's one row with a P in the middle, what does this say? explain select count(*) from d_trr_iw where ast_revision like 'B2.P%.SX' or pick a value that you know can't exist e.g. if ast_revision can never start with WW then explain select count(*) from d_trr_iw where ast_revision like 'WW.%.SX' klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LIKE not using indexes (due to locale issue?)
Ow Mun Heng wrote: explain select * from d_trr where revision like '^B2.%.SX' --where ast_revision = 'B2.M.SX' Seq Scan on d_trr (cost=0.00..2268460.98 rows=1 width=16) Filter: ((revision)::text ~~ '^B2.%.SX'::text) show lc_collate; en_US.UTF-8 Is it that this is handled by tsearch2? Or I need to do the locale to C for this to function? See http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html. It tells you how to create an index that like might use in non-C locales. As a side note, the ^ in the string above has no special meaning for like. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with volatile function
Artacus wrote: So my understanding of volatile functions is that volatile functions can return different results given the same input. I have a function random(int, int) that returns a random value between $1 and $2. I want to use it in a query to generate values. But it only evaluates once per query and not once per row like I need it to. -- This always returns the same value SELECT ts.sis_id, bldg_id, f_name.name, l_name.name FROM tmp_students ts JOIN names AS f_name ON ts.gender = f_name.gender WHERE f_name.counter = random(1,300) --As does this SELECT ts.sis_id, bldg_id, f_name.name, l_name.name FROM tmp_students ts JOIN names AS f_name ON ts.gender = f_name.gender AND ts.counter = random(1,100) -- This generates different numbers SELECT random(1,100), s.* FROM usr_students s Are you sure it's volatile? (as opposed to stable) postgres=# create or replace function random(int,int) returns int as $$select round($2*random()*$1)::int;$$ language sql volatile; CREATE FUNCTION postgres=# select generate_series from generate_series(1,10,1) where random(1,100) 50; generate_series - 1 3 4 6 7 9 (6 rows) postgres=# select generate_series from generate_series(1,10,1) where random(1,100) 50; generate_series - 3 7 8 9 (4 rows) postgres=# select random(1,100), generate_series from generate_series(1,10,1); random | generate_series +- 56 | 1 23 | 2 80 | 3 57 | 4 16 | 5 99 | 6 9 | 7 41 | 8 90 | 9 88 | 10 (10 rows) postgres=# create or replace function random(int,int) returns int as $$select round($2*random()*$1)::int;$$ language sql stable; CREATE FUNCTION postgres=# select generate_series from generate_series(1,10,1) where random(1,100) 50; generate_series - 1 2 3 4 5 6 7 8 9 10 (10 rows) postgres=# select generate_series from generate_series(1,10,1) where random(1,100) 50; generate_series - (0 rows) postgres=# select random(1,100), generate_series from generate_series(1,10,1); random | generate_series +- 72 | 1 90 | 2 53 | 3 47 | 4 53 | 5 33 | 6 10 | 7 56 | 8 78 | 9 87 | 10 (10 rows) postgres=# -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] inserting to a multi-table view
Michael Shulman wrote: On Mon, Jun 16, 2008 at 10:03 PM, Scott Marlowe [EMAIL PROTECTED] wrote: I can write a trigger function that does the right thing, with 'INSERT ... RETURNING person_id INTO ...', but Postgres will not let me add an INSERT trigger to a view; it says 'ERROR: studentinfo is not a table'. Got a short example of what you've tried so far? create function ins_st() returns trigger as $$ declare id integer; begin insert into person (...) values (NEW) returning person_id into id; insert into student (person_id, ...) values (id, NEW); end; $$ language plpgsql; create trigger ins_student before insert on studentinfo for each row execute procedure ins_st(); ERROR: studentinfo is not a table Mike The only way I could find to make this work is to use a rule and wrap the inner insert returning in a function. create or replace function newperson (studentinfo) returns setof person as $$ declare arec person%rowtype; begin for arec in insert into person (foo,bar) values ($1.foo,$1.bar) returning * loop -- insert into address (...) values (arec.person_id, $1) -- insert into phone (...) values (arec.person_id, $1) return next arec; end loop; return; end; $$ language plpgsql volatile; create rule atest as on insert to studentinfo do instead ( insert into student (person_id) select (select person_id from newperson(new)); ); klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] inserting to a multi-table view
Michael Shulman wrote: On Tue, Jun 17, 2008 at 3:46 AM, Klint Gore [EMAIL PROTECTED] wrote: The only way I could find to make this work is to use a rule and wrap the inner insert returning in a function. Thanks, this works! Although it feels like something of a hack; shouldn't there be a more elegant solution? The more elegant statements don't work. The ideal solution would be if some variant of insert into student (person_id) values ((insert into person (...) values (...) returning person_id)); worked. Also, I don't understand why create rule atest as on insert to studentinfo do instead ( insert into student (person_id) select (select person_id from newperson(new)); ); is necessary; what is wrong with create rule atest as on insert to studentinfo do instead ( insert into student (person_id) select person_id from newperson(new); ); ? (Other than the evident fact that it doesn't work; the error message function expression in FROM cannot refer to other relations of same query level is not illuminating to me.) Got it in 1 - it doesn't work. I'm sure there's a good reason for the error message that someone who knows more about rules can explain. Additionally, is there a reason why you put one of the inserts in the function and the other in the rule? Why not have the function do both inserts and then the rule just invoke the function? If the rule does the insert into student, then the return to the application looks like a normal insert (e.g. you can check rows affected). klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help Recovering from Botched Upgrade Attempt
Rich Shepard wrote:
Despite trying to be careful, I managed to mess up the upgrade from -8.1.4
to -8.3.3 on my Slackware-11.0 server/workstation. I expect that someone
here will see my error and point me in the right direction to recover a
working dbms.
Here's what I did:
1.) As a user, I ran pg_dumpall on version 8.1.4 and had that written to
/usr4/postgres-backups/.
2.) Created /usr4/pgsql_old/, and copied all of /var/lib/pgsql/ there ('cp
-a /var/lib/pgsql/* .')
3.) In /usr4/pgsql_old/data/postgresql.conf, changed port to 5466.
4.) As root, ran '/etc/rc.d/rc.postgresql stop', which reported that's
what it did.
You copied the files without stopping the database? move 4 to 2.
5.) Built postgresql-8.3.3 using the SlackBuild script, then ran
'upgradepkg postgresql-8.3.3*tgz'; other than reporting not finding an
expected pid file, that went smoothly.
Is there an initdb in here somewhere? Or is the 8.3 server trying to
start with an 8.1 file structure?
6.) Tried starting the new version, /etc/rc.d/rc.postgresql start. It
reported that the server was already running, but that it started anyway.
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help Recovering from Botched Upgrade Attempt
Rich Shepard wrote: On Wed, 18 Jun 2008, Klint Gore wrote: 5.) Built postgresql-8.3.3 using the SlackBuild script, then ran 'upgradepkg postgresql-8.3.3*tgz'; other than reporting not finding an expected pid file, that went smoothly. Is there an initdb in here somewhere? Or is the 8.3 server trying to start with an 8.1 file structure? Klint, Backed up a couple of steps, and tried again. Removed postgresql-8.3.3; deleted all contents of /var/lib/pgsql/data (because initdb is supposed to create the contents, if I correctly read the Postgresql book); re-installed postgresql-8.3.3; ran (as user postgres) 'initdb -D /var/lib/pgsql/data'. Nothing! I've really FUBARed this and don't understand how, or what to do to recover. Thanks, Make sure that initdb is the version you want initdb --version then initdb -E UTF8 -D /var/lib/pgsql/data then post the output of that. kllint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multithreaded queue in PgSQL
Stevo Slavić wrote: I'm trying to make implementation more generic, not to use Postgres specific SQL, and through Hibernate and Spring configuration make services acquire lock on batch of rows, when trying to acquire lock on batch of rows an exception should be thrown if rows are already locked by a different service, and through that exception I intend to signal to other services that they should try to handle and acquire lock on next batch of rows. Will see how that goes. It's postgres specific, but a serializable transaction and update/returning fits with how you want it to act. begin transaction isolation level serializable; update newsletter_recipients set where (...) in (select ... from newsletter_recipients where not_sent or crashed limit 10) returning *; commit; The update marks the rows as processing. The returning gives the selected ones back to the application without having to issue a select and an update. The serializable transaction throws an error in other threads that try to claim the same rows. You could add an offset to the limit to try to select different rows. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql \e command
Volkan YAZICI wrote: 2. It would be really neat to be able to issue \et regex - Edit table. (Create script of table will get dumped.) \et+ regex - Edit table with dependents. (With create script of INDEXes, triggers, etc.) How do you intend to use it? postgres=# create table bar (foo int); CREATE TABLE postgres=# \e ERROR: relation bar already exists postgres=# You'd have to comment out the create table and then write all the alter table statements to get the changes made. \ef regex - Edit function. Yes. Like what pgadmin does when you hit the sql button with a function selected. postgres=# create or replace function foo() returns int as $$ select 1; $$ language sql; CREATE FUNCTION postgres=# \e CREATE FUNCTION postgres=# klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] does postgresql works on distributed systems?
Justin wrote: aravind chandu wrote: Hi, My question is Microsoft sql server 2005 cannot be shared on multiple systems i,e in a network environment when it is installed in one system it cannot be accessed one other systems. This don't make any sense. Are your taking about sharing the actual mdb files or access the service itself??? This question is just confusing. Your Questions are confusing can you clarify I'm guessing at what you mean??? He's talking about having the raw database files on a file server (eg SMB share). DB's like firebird and sqlite can handle this way of accessing the data using the embedded engines. Aravind - read http://msdn.microsoft.com/en-us/library/ms190611.aspx and it should help you understand how to database servers (including postgres) work in network environments. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is it a bug in rule system?
laser wrote: It's not a bug, just your misunderstanding of how rules work. Rules rewrite queries. What happen in your case is because of the condition your query will be split into two: once with your INSERT with a NOT EXISTS (subquery) and once as an UPDATE with the condition EXISTS (subquery). So the first query will insert with id=1 and then the update sees this row and updates it to 2. What this says is that rules are the wrong tool for what you're trying to do. Conditional rules are powerful but not appropriate here. Have a nice day, Thanks to clarify, if it's a misunderstanding of rule, then I'll some how confuse with DO INSTEAD vs. DO ALSO rule, isn't DO INSTEAD will replace original INSERT with the one provided in CREATE RULE? ...after some rethinking, can I understand what happened as below step? 1, INSERT will be rewrite into a INSERT with a EXIST condition clause and a UPDATE statement; 2, when NOT EXISTS, INSERT succeed, and the query tree in 1 proceed to UPDATE; 3, the UPDATE saw the INSERT in 2, then UPDATE it; if so, then I understand what happed there, and it's surly not a bug but a mis-use of rule. It took me a while to get my head around it but essentially the query tree gets rewritten to 1. execute original statement where not rule.condition 2. execute rule body where rule.condition In this case, part 1 changes the result of the rule condition for part 2 making both execute. I.E. rule condition is evaluated every time it is referenced rather than being kept constant for both query tree entries. To do it in a trigger, you would have to do a before insert trigger that looks like update ruleTest set id = id+1 where ruleTest.name = NEW.name; if rowsaffected 0 then return null; end if; return new; klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] small table, huge table, and a join = slow and tough query. cake inside!
edfialk wrote: So, what I need is all kinds of things, but to start (pseudo-query): SELECT small.fips, small.geom, small.name, SUM(huge.value) from small JOIN huge on huge.fips = small.fips WHERE (SUM(huge.value)) 500 AND huge.pollutant='co'; wonder if that makes sense. Obviously, can't have an aggregate in where clause, so I've tried a couple WHERE (SELECT) kind of things, nothing working out too well. So first, if anyone has any idea on the best way I can do a WHERE (sum(huge.value) 500) See GROUP BY and HAVING or... any ideas on how I could speed up the query, I would be so extremely grateful. What columns are primary keys or indexed? Run this and post what it says vacuum; analyze; explain insert query here If you can wait for the query to finish, change the last line to explain analyze ... klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Download HTML documentation?
Thomas Kellerer wrote: Hi, I'm trying to download the HTML version of the manual (8.3.1). It does not seem to be part of the Windows installer any more. Instead the a chm version is included. At http://www.postgresql.org/docs/manuals/ it says the HTML can be downloaded in the FTP area, but I cannot find any download that sounds like it contained the pre-built HTML manual. At least no directory or file with doc or manual in its name. What am I missing? Did you look in the dev directory of the ftp? http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fdev%2Fdoc%2Fpostgres.tar.gz klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] triggers: dynamic references to fields in NEW and OLD?
[see below]
Kerri Reno wrote:
Vance,
I missed your earlier post, so I may be misunderstanding the
situation, but I think you could do this more easily in plpython,
because TD['new'] and TD['old'] are dictionaries, and you can traverse
the dictionaries like this:
for k, v in TD['new'].items():
if tblfld == k:
plpy.notice('%s' % v)
This probably looks like gibberish if you're not used to python, but
if you'd like more help, email me back (with your original post) and
I'll get back to you next week.
Kerri
On 5/15/08, *Vance Maverick* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Thanks! Your solution clearly works, but it requires the shared
function to
enumerate all possible column names. In my real case, there are 8-10
distinct names, so that's a bit uglybut it works.
Vance
-Original Message-
If you just need which table triggered the function then
|TG_TABLE_NAME| may
be simpler than passing parameters.
Something like this will probably work for you (replace the raise
notice
with whatever you have to do)
create or replace function atest() returns trigger as $$ declare
avalue int;
tblfld text;
begin
tblfld := tg_argv[0];
if tblfld = 'aa' then
avalue := new.aa;
else
if tblfld = 'bb' then
avalue := new.bb http://new.bb;
end if;
end if;
raise notice '%',avalue;
return new;
end;
$$ language plpgsql;
klint.
Agree with Kerri - do it in one of the languages other than plpgsql.
Plpgsql can't do the for loop as simply as other languages. There's no
way to walk a record structure (new) as a collection/array and pull out
the item you are interested in.
You could possibly cheat by putting new into a temp table and then
executing a select on it. Performance will probably be bad.
create temp table newblah as select new.*;
execute 'select new. ' || tg_argv[0] || '::text' ||
' from newblah new ' into newval;
execute 'drop table newblah';
There probably is a function in the plpgsql internals that will pull a
named field out of a record but I have no idea what it is or if it's
exposed so that it can be called. Maybe someone who knows about the
internals of plpgsql could comment - is there a function like
getfieldfromrecord(record,text)?
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] triggers: dynamic references to fields in NEW and OLD?
Vance Maverick wrote:
I have a bunch of tables that are similar in some ways, and I'm about to
put triggers on them. The triggers will all do essentially the same
thing -- the only wrinkle is that the name of the column they operate on
varies from table to table. I'd like to have just one trigger function,
written 'dynamically' so it can take the name of the column as a trigger
parameter (in TG_ARGV). For example, given tables
CREATE TABLE a (aa INT);
CREATE TABLE b (bb INT);
I'd like to be able to write a trigger function foo() such that with
trigger declarations
CREATE TRIGGER a_foo AFTER INSERT OR UPDATE OR DELETE ON a
FOR EACH ROW EXECUTE PROCEDURE foo('aa');
CREATE TRIGGER b_foo AFTER INSERT OR UPDATE OR DELETE ON b
FOR EACH ROW EXECUTE PROCEDURE foo('bb');
the logic in foo() reads columns a.aa or b.bb respectively.
I've tried composing a SQL string including the text 'NEW.aa' or
'NEW.bb' appropriately, and then passing this to EXECUTE. This fails:
ERROR: NEW used in query that is not in a rule
Any suggestions?
If you just need which table triggered the function then |TG_TABLE_NAME|
may be simpler than passing parameters.
Something like this will probably work for you (replace the raise notice
with whatever you have to do)
create or replace function atest() returns trigger as $$
declare
avalue int;
tblfld text;
begin
tblfld := tg_argv[0];
if tblfld = 'aa' then
avalue := new.aa;
else
if tblfld = 'bb' then
avalue := new.bb;
end if;
end if;
raise notice '%',avalue;
return new;
end;
$$ language plpgsql;
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is this possible in a trigger?
Fernando wrote: I want to keep a history of changes on a field in a table. This will be the case in multiple tables. Can I create a trigger that loops the OLD and NEW values and compares the values and if they are different creates a change string as follows: e.g; FOR EACH field IN NEW IF field.value OLD.field.name THEN changes := changes || field.name || ' was: ' || OLD.field.value || ' now is: ' || field.value || '\n\r'; END IF END FOR; Your help is really appreciated. You can't in plpgsql. It doesn't have the equivalent of a walkable fields collection. Its possible in some other procedure languages (I've seen it done in C). Having said that, you might be able to create new and old temp tables and then use the system tables to walk the columns list executing sql to check for differences. something like create temp table oldblah as select old.*; create temp table newblah as select new.*; for arecord in select columnname from pg_??columns?? join pg_??tables?? on ??columns??.xxx = ??tables??.yyy where tablename = oldblah and pg_table_is_visible loop execute 'select old.' || arecord.columname || '::text , new. ' || arecord.columname || '::text' || ' from oldblah old, newblah new ' || ' where oldblah.' || arecord.columnname || ' newblah.' ||arecord.columnnameinto oldval,newval; changes := changes || arecord.columnname || ' was ' || oldval || ' now ' || newval; end loop; execute 'drop table oldblah'; execute 'drop table newblah'; performance could be awful though. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] operator is not unique: integer || integer
Daniel Schuchardt wrote: Tino Wildenhain schrieb: RETURN extract(year FROM $1)*100+extract(month FROM $1)-1; was too clean and easy? ;)) Looks like a good oportunity to clean up your code before anything unexpected happens :-) Cheers T. LOL. Yes I don't like such easy things :-P RAISE NOTICE error during validation % :, 'ks:'||ks||'@'||loopdate||''; (here LoopDate is a DateTime) You know you can use more than one % in a raise and it will take care of the data types? create function atest() returns integer as $$ declare ks integer; loopdate timestamp; begin ks := 3; loopdate := now(); raise notice 'blah ks:[EMAIL PROTECTED]', ks, loopdate; return 1; end; $$ language plpgsql; postgres=# select atest(); NOTICE: blah ks:[EMAIL PROTECTED] 09:58:55.812 atest --- 1 (1 row) klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trouble with text search select statement
Mont Rothstein wrote:
I am having trouble getting a multiple column text search select
statement to work.
I've setup the following:
CREATE TABLE tstest (col1 text, col2 text);
INSERT INTO tstest (col1, col2) VALUES ('bird', 'moon');
CREATE INDEX tstest_ts_idx ON tstest USING gin(to_tsvector('english',
coalesce(col1, '') || '' || coalesce(col2, '')));
I then execute what I believe is the correct select statement:
SELECT * FROM tstest WHERE to_tsvector('english', coalesce(col1, '')
|| '' || coalesce(col2, '')) @@ to_tsquery('english', 'bird');
If I remove the second column from the to_tsvector in the select
statement then it returns the row.
SELECT * FROM tstest WHERE to_tsvector('english', coalesce(col1, ''))
@@ to_tsquery('english', 'bird');
I know I've missed something basic but I can't figure out what.
As a side note I don't understand the purpose of the || '' || between
the columns in the to_tsvector. I saw it in several examples. I've
tried both with and without it with no change.
'' isn't supposed to to be the empty string, it's supposed to be a
quoted space. to_tsvector separates on whitespace. So with the space,
it will have the words bird and moon.Without the space, it will have the
single word birdmoon.
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] inheritance...
Tom Allison wrote: Am I missing something in the fine print? fine print = see 5.8.1 Caveats on http://www.postgresql.org/docs/8.3/interactive/ddl-inherit.html klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table with differerent Data Types
xaviergxf wrote: Hi, I´m trying to do the follow: create a table like: create table t( cod serial, data_type char(10), value ??? ); I would like to do the follow the table would tell me what data type its the value. For instance: insert into t values(1, 'Integer', 12); insert into t values(2, 'String', 'test'); insert into t values(3, 'Float', 1.23); How can i do that? Its that possible? How Can i solve this problem? see http://www.varlena.com/varlena/GeneralBits/110.php klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: FW: Re: [GENERAL] create temp in function
Kerri Reno wrote: So the reason I'm getting the error is that I'm running it in 8.0. Thanks so much for your help! Kerri use FOR instead CREATE OR REPLACE FUNCTION private.get_salary_schedule(integer, text) RETURNS record AS $BODY$ declare tcp_id alias for $1; tfy alias for $2; tbl text := 'saltab' || tfy; arow record; query text; begin query = 'select ' || 'salary_schedule, pay_column, step from ' || tbl || ' where cp_id = ' || to_char(tcp_id,''); raise notice 'query: %', query; for arow in execute query loop return arow; end loop; return null; end; $BODY$ LANGUAGE 'plpgsql' STABLE SECURITY DEFINER; you probably need to protect tfy from sql injection too. see quote_ident. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Insert Rewrite rules
David wrote:
I am having problems with the rewrite rules though it seems to be skipping over
any rule that has a where statement in it ie
CREATE OR REPLACE RULE firewall_test AS ON INSERT TO firewall DO INSTEAD INSERT INTO
firewall_y2008m04d21 VALUES(NEW.time);
INSERT INTO firewall (time) VALUES('2008-04-21 12:00:00');
INSERT 1029459 1
works but
CREATE OR REPLACE RULE firewall_test AS ON INSERT TO firewall WHERE TRUE DO INSTEAD
INSERT INTO firewall_y2008m04d21 VALUES(NEW.time);
INSERT INTO firewall (time) VALUES('2008-04-21 12:00:00');
ERROR: No inserting to firewall please
doesn't. I have placed a trigger on the table to prevent anything from
inserting into the top level table hence the error.
an example of the full rule we are trying to use that doesn't work is
firewall_y2008m04d21_insert AS
ON INSERT TO firewall
WHERE new.time = '2008-04-21 00:00:00'::timestamp without time zone AND new.time '2008-04-22 00:00:00'::timestamp without time zone DO INSTEAD INSERT INTO firewall_y2008m04d21 (time, firewall, pri, sent, rcvd, lsent, lrcvd, duration, src, dst, arg, msg, ref, dstname, srcname, agent, server, srcclass, dstclass, rule, username, proto, op, result, vpn, type, cache, direction, content, fwdfor, coninfo, tcpflags, method, action, policy, service, engine, state, fwid, block, authprofile, summarised, realm, clientmac, account, count, interface)
VALUES (new.time, new.firewall, new.pri, new.sent, new.rcvd, new.lsent, new.lrcvd, new.duration, new.src, new.dst, new.arg, new.msg, new.ref, new.dstname, new.srcname, new.agent, new.server, new.srcclass, new.dstclass, new.rule, new.username, new.proto, new.op, new.result, new.vpn, new.type, new.cache, new.direction, new.content, new.fwdfor, new.coninfo, new.tcpflags, new.method, new.action, new.policy, new.service, new.engine, new.state, new.fwid, new.block, new.authprofile, new.summarised, new.realm, new.clientmac, new.account, new.count, new.interface)
There is one of these for each day with only the times changing.
Am I missing something or is this just broken?
Have you got a statement trigger or a row trigger?
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Client Authentication
Stefan Sturm wrote: Hello, I set up a PostgreSQL 8.3.1 Server on my Webserver( located somewhere in the web ). On my local Server I use the trust method to access the server. But on my Webserver I want to use an user with password to access the Server. So I did the following: I created a user( as superuser ) with: createuser -P username Then I add a new line to the pg_hba.conf file: hostall username 0.0.0.0 http://0.0.0.0 md5 I want to access all databases from all IP-Adresses with this user. But this fails :-( WHere is my Error? I hope some can help me. try adding a subnet mask host all username 0.0.0.0 0.0.0.0 md5 or a cidr mask host all username 0.0.0.0/0 md5 klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg 8.3.0 unexpected sending network packet?
My local firewall came up with a warning about postgres wanting to send a network packet and I'm wondering if anyone can shed some light on its trying to do? The only line in pg_hba.conf that's not commented is hostall all 127.0.0.1/32 trust According to netstat -a, its only listening on 127.0.0.1:5432 The sender details matches to my computer. 129.180.49.84 is just another machine on the local network (not dhcp server/dns server/wins server/default gateway) - firewall message -- Parent Process :C:\Program Files\PostgreSQL\8.3\bin\postgres.exe Parent Version :8.3.0.831 Parent Description :PostgreSQL Server Parent Process ID :0xB8 (Heximal) 184 (Decimal) File Version :8.3.0.831 File Description :PostgreSQL Server (postgres.exe) File Path :C:\Program Files\PostgreSQL\8.3\bin\postgres.exe Process ID :0xBA4 (Heximal) 2980 (Decimal) Connection origin :local initiated Protocol :Raw Ethernet Local Address : 0.0.0.0 Local Port :0 Remote Name : Remote Address :0.0.0.0 Remote Port : 0 Ethernet packet details: Ethernet II (Packet Length: 56) Destination: ff-ff-ff-ff-ff-ff Source: 00-12-3f-e9-e8-49 Type: ARP (0x0806) Address Resolution Protocol (ARP) Hardware type: Ethernet (0x0001) Protocol type: IP (0x0800) Hardware size: 6 Protocol size: 4 Opcode: Request Sender hardware address: 00-12-3f-e9-e8-49 Sender IP address: 129.180.49.116 Target hardware address: 00-00-00-00-00-00 Target IP address: 129.180.49.84 Binary dump of the packet: : FF FF FF FF FF FF 00 12 : 3F E9 E8 49 08 06 00 01 | ?..I 0010: 08 00 06 04 00 01 00 12 : 3F E9 E8 49 81 B4 31 74 | ?..I..1t 0020: 00 00 00 00 00 00 81 B4 : 31 54 30 4C 02 01 00 04 | 1T0L 0030: 06 70 75 62 6C 69 63 A0 : | .public. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY to STDOUT and pipes
kevin kempter wrote: Hi List; I want to run a copy (based on a select) to STDOUT and pipe it to a psql copy from STDIN on a different host. here's what I have: 1) a .sql file that looks like this: copy ( select cust_id, cust_name, last_update_dt from sl_cust ) to STDOUT with delimiter '|' This works. However I want to pipe the resulting data into a psql statement that does a copy IN to a table on another host. I can't seem to get it right. I tried this: psql -f file1.sql | psql -h newhost -f file2.sql where file1.sql is the copy statement above and file2.sql does a copy table from STDIN with delimiter '|' Any thoughts on what I'm doing wrong? no database unless your logged in as someone with the same name as the databases on both servers? C:\psql -d gpdms_nunit -c copy (select * from pg_class) to stdout with delimiter '|' | psql -d gpdms -c create table foo (like pg_class); copy foo from stdin with delimiter '|'; works for me on 8.3.0 win32 klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with async notifications of table updates
Tom Lane wrote: Tyler, Mark [EMAIL PROTECTED] writes: What I want to do is to guarantee that the row is available for selection prior to sending the message. You cannot do that with an AFTER trigger, because whatever it does necessarily happens before your transaction commits. I suggest rethinking your dislike of NOTIFY. What if the trigger is a constraint trigger that is deferred? http://www.postgresql.org/docs/8.3/interactive/sql-createconstraint.html klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rule question
[see below or the top posting police will arrive on my doorstep :)] Devi wrote: Hi, CREATE RULE dosen't require any lock. It is carried out in the parser level. But there will be ACCESS SHARE lock over the tables which are being queried are acquired automatically. Thanks DEVI.G - Original Message - From: Tim Rupp [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Friday, February 29, 2008 8:47 AM Subject: [GENERAL] rule question Hey list, Does CREATE RULE require an exclusive lock on the table it's making a rule for? For instance, if an insert is being done on the table, and you do 'create rule', it will wait for said insert to finish? Thanks, -Tim Seems to me like needs an exclusive lock. I setup 2 sessions. first one idle in transaction after an insert and then issued the create rule in the other. the 2nd one sat there. pg_locks in the 1st one said # select * from pg_locks where relation = 20404; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid |mode | granted --+--+--+--+---++---+-+---+--++--+-+ - relation |16770 |20404 | | | | | | | | 1/921 | 632 | RowExclusiveLock| t relation |16770 |20404 | | | | | | | | 2/771 | 3812 | AccessExclusiveLock | f (2 rows) (ignore the formatting the important bit is pid, mode, granted) 3812 is the pid of my create rule according to pg_backend_pid() and 632 is my insert transaction. Execution of the rule follows what you were saying. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to monitor the progress of a stored procedure?
Kynn Jones wrote: (Sorry for asking so many questions!) What techniques can one use to monitor the progress of a stored procedure? Specifically, how can I get the procedure to print a progress indicator message to the screen every once in a while? [...](In case it matters, the procedure is written in PL/perl. I tried to use Perl's print statement, unbuffered, to print out a message periodically from within the procedure, but I see no output.) elog? (see example in http://www.postgresql.org/docs/8.3/interactive/plperl-database.html) klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] copy question - fixed width?
Is there any way to make copy work with fixed width files? eg create table t1 (code char(5), description char(30)); copy t1 from '/tmp/afile' delimiter as nothing? where afile looks something like 1test16789012345678901234567890 2test26789012345678901234567890 3test36789012345678901234567890 at the moment i have a function that does create temp table tmp1 (line text); copy tmp1 from '/tmp/afile'; bunch of code to create a view of tmp1 with lots of substrings from looking at information schema for t1 definition it used to do the substrings in the plpgsql but it was orders of magnitude quicker to use the view insert into t1 select * from tmpview; klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] changing 'mons' in interval?
Is there a way to change mons in interval::text to the full word months without resorting to replace(aninterval::text,'mon','Month')? If it can handle locales as well that would be good (but I could live without it). klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] changing 'mons' in interval?
On Mon, 4 Jun 2007 11:43:08 +0200, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Mon, Jun 04, 2007 at 06:51:37PM +1000, Klint Gore wrote: Is there a way to change mons in interval::text to the full word months without resorting to replace(aninterval::text,'mon','Month')? If it can handle locales as well that would be good (but I could live without it). Have you considered using to_char to get the output in the exact format you want? Yes. It turned out to be a fairly complex statement with case's of extracts to handle plurals and negatives. That's what lead me to asking. besides http://www.postgresql.org/docs/7.4/interactive/functions-formatting.html Warning: to_char(interval, text) is deprecated and should not be used in newly-written code. It will be removed in the next version. http://www.postgresql.org/docs/8.0/interactive/functions-formatting.html Warning: to_char(interval, text) is deprecated and should not be used in newly-written code. It will be removed in the next version. And 8.1/8.2 seem to have been revived that warning but still has to_char(interval,text) klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Views- Advantages and Disadvantages
On Thu, 10 May 2007 00:06:06 -0400, Tom Lane [EMAIL PROTECTED] wrote: ... I suspect the important point here is that if you have CREATE VIEW v AS SELECT sis, boom, bah ... then SELECT ... FROM ..., v, ... will be rewritten to the same parsetree as if you'd written SELECT ... FROM ..., (SELECT sis, boom, bah ...) AS v, ... and then everything hinges on what the planner is able to do with that. In simple cases the planner is able to flatten the sub-SELECT together with the outer query and you get a reasonable plan, but if it fails to do that then you might get a pretty bad plan. I think some people might complain that views are slow because they compared the view to a case that is not exactly the above mechanical transformation, but one where they had applied some simplification/optimization that was obvious to them but not to the planner. I think I have a classic example of this (for older pg versions anyway) - we have a lot of views with a left join in them and performance is awful when the view is inner joined to another table. select v.* from v where key_of_1st_table = blah takes a small fraction of a second. select v.* from v join analysed_tmp_containing_only_blah using (key_of_1st_table) takes a coffee and a doughnut. The outer join reordering in 8.2 should solve this situation though? klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Login to Postgres 8.2 via Cygwin hangs
On Wed, 18 Apr 2007 23:25:07 -0400, Sergei Dubov [EMAIL PROTECTED] wrote: I just installed the latest release of Postgres (8.2 native version) on Win XP Pro). When I try to run psql through Cygwin, I cannot get to the postgres terminal. I mean I type in the password, and the feeling is that it just hangs. It all works fine from Windows terminal. Any ideas at all? Use strace and see where it stops. If that doesn't help, use gdb. klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] dollar-quoting trouble
On Thu, 19 Apr 2007 23:45:47 +0200, Karsten Hilbert [EMAIL PROTECTED] wrote: I can't spot the trouble with this function definition: create function dem.trf_null_empty_title() returns trigger language plpgsql as $null_empty_title$ begin if (NEW.title is null) then return NEW; end if; if trim(NEW.title) '' then return NEW; end if; NEW.title := NULL; return NEW; end; $null_empty_title$; PostgreSQL 8.1.8 (Debian/Etch) is telling me: psql:dem-identity.sql:43: ERROR: unterminated dollar-quoted string at or near $null_empty_title$ begin if (NEW.title is null) then return NEW; at character 83 Does psql --version match select version()? There's a message in the archive pgsql-bugs where this happened. klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] COPY FROM - how to identify results?
On Tue, 03 Apr 2007 12:45:54 -0400, Jaime Silvela [EMAIL PROTECTED] wrote: I'd like to be able to do something like COPY mytable (field-1, .. field-n, id = my_id) FROM file; How do you get my_id? Can you get it in a trigger? Triggers still fire with copy so if you can get a trigger to fill in the id column you can copy with just the field names. klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Using MS Access front-end with PG]
On Tue, 03 Apr 2007 18:24:00 -0700, Joshua D. Drake [EMAIL PROTECTED] wrote:
Paul Lambert wrote:
Tom Lane wrote:
Paul Lambert [EMAIL PROTECTED] writes:
or the case sensitivity?
That could be attacked in a few ways, depending on whether you want
all text comparisons to be case-insensitive or only some (and if so
which some). But it sounds like MS SQL's backward standards for
strings vs identifiers has got you nicely locked in, as intended :-(
so there may be no point in discussing further.
I don't have any case sensitive data - so if sensitivity could be
completely disabled by a parameter somewhere, that would be nice.
You could preface all your queries with something like:
select * from foo where lower(bar) = lower('qualifer');
But that seems a bit silly.
Is there any way to create operators to point like to ilike? There
doesn't seem to be a like or ilike in pg_operator (not in 7.4 anyway).
klint.
+---+-+
: Klint Gore: Non rhyming:
: EMail : [EMAIL PROTECTED] : slang - the:
: Snail : A.B.R.I.: possibilities :
: Mail University of New England : are useless :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---+-+
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Approximate join on timestamps
On Tue, 20 Mar 2007 23:30:46 +, Phil Endecott [EMAIL PROTECTED] wrote: I have two tables containing chronological data, and I want to join them using the timestamps. The challenge is that the timestamps only match approximately. My first attempt was something like t1 join t2 on (abs(t1.t-t2.t)'1 min'::interval) Of course there is no abs for intervals, and I couldn't think of anything better than this t1 join t2 on (t1.t-t2.t'1 min'::interval and t2.t-t1.t'1 min'::interval) How about using extract(epoch from t) to turn it into a numeric value? select distinct on (t1.primary_key) * from t1 join t2 on extract(epoch from t2.t) extract(epoch from t1.t) + 30 and extract(epoch from t2.t) extract(epoch from t1.t) - 30 order by t1.something, abs(extract(epoch from t2.t) - extract(epoch from t1.t)); What indexes could I add to make this moderately efficient? If t is timestamp without time zone then you might be able to use an index on it create index t1_epoch_idx on t1 ((extract(epoch from t))) create index t2_epoch_idx on t2 ((extract(epoch from t))) But that query isn't really good enough. There is no single epsillon value that works for this data set. I really want to find the closest match. see order by. the +/- 30 in the above query can be used for tolerance on the join. (One detail is that the left table has fewer rows than the right table, and I want one output row for each row in the left table.) see distinct on. klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Debugging Server Code
On Wed, 14 Mar 2007 12:56:25 -0700, Joshua D. Drake [EMAIL PROTECTED] wrote: It certainly would be great. IIRC there is actually one for plpgsql over at pgfoundry. Do you remember what it's called? In the search for plpgsql I get pgflows, plpsm, and enterprise db's debugger (which the summary says requires enterprise db 8.1.4 or wait for 8.3 if the patch is submitted/accepted) The only one I've seen is do it is EMS SQL Manager for a mere $275. klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Spam from EnterpriseDB?
On Thu, 18 Jan 2007 17:08:53 -0800, Joshua D. Drake [EMAIL PROTECTED] wrote: Alan Hodgson wrote: On Thursday 18 January 2007 16:44, Ron Johnson [EMAIL PROTECTED] Harsh, aren't we? Rich and Garland weren't peddling pr0n or a pump-and-dump stock scam. The fact that they've lost some (a lot of?) respect from potential customers will be pain enough. Spam is spam. I don't care what they're selling. Anyone dumb enough to send spam in 2006 should be fired on the spot. That is a bit extreme. One persons SPAM is another persons interesting information. Although I agree that the behavior in this particular situation was a bit less than the average IQ score. If you are going to communicate with potential customers, especially as SPAM have the integrity to do it yourself and take the heat yourself. Don't use some off brand secondary service and pay them to spam for you. I send out email all the time to potentials. It is common practice but I do it, directly as me. I think that this bit is the spam clincher Please click here to unsubscribe [http://now.eloqua.com/] Without that, I might have even filed it away in case I needed what they were offering (24/7 phone support is useful for those of us in vastly different time zones). Mailing lists that I didn't ask to be on is a different story. klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] primary keys
On Wed, 19 Apr 2006 19:39:45 -0700, Orion Henry [EMAIL PROTECTED] wrote: I'm trying to craft a query that will determine what column(s) are the primary key for a given table. I have succeeded but the query is so ugly that it borders on silly and cannot work for an arbitrary number of tables since indkey is an int2vect and the ANY keyword does not work on it. Please tell me there's an easier way to do this. Here is the query for tablename $table. SELECT attname FROM pg_index JOIN pg_class ON (indrelid = pg_class.oid) JOIN pg_attribute ON (attrelid = pg_class.oid) WHERE indisprimary IS TRUE AND (attnum = indkey[0] OR attnum = indkey[1] OR attnum = indkey[2]) AND relname = '$table'; Orion works for me on version 8.1.3 SELECT attname FROM pg_index JOIN pg_class ON (indrelid = pg_class.oid) JOIN pg_attribute ON (attrelid = pg_class.oid) WHERE indisprimary IS TRUE AND attnum = any(indkey) AND relname = $tablename; or on v7 you could try select pcl.relname, (select array_accum(attname) from pg_attribute where attrelid = pco.conrelid and attnum = any(pco.conkey)) as cols from pg_constraint pco join pg_class pcl on pcl.oid = pco.conrelid where pcl.relname = $tablename and pco.contype = 'p' klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Is this possible.
On Thu, 30 Mar 2006 10:45:20 +1100, Harvey, Allan AC [EMAIL PROTECTED] wrote: Hi all, Can sombody please help me realise a function, the intent as described by... -- Function to create the table for a new point CREATE OR REPLACE FUNCTION make_table( varchar ) RETURNS VARCHAR AS ' CREATE TABLE $1( parameter varchar(8) NOT NULL, value float NOT NULL, dt timestamp NOT NULL ); execute ''create table ''||quote_ident($1)||'' (...)''; CREATE INDEX $1_dtindex ON $1( dt ); execute ''create index ''||quote_ident($1)||''_dtindex ...''; SELECT $1; return $1; ' LANGUAGE SQL; ' language plpgsql; quote_ident function should help with sql insertion attack. klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] FAQ 1.1
On Tue, 28 Mar 2006 10:10:15 +0930 (CST), Michael Talbot-Wilson [EMAIL PROTECTED] wrote: How, really, do people pronounce PostgreSQL? we just use postgres. They say that SQL should be pronounced Sequel and I've even met people who do that. I hate that. It's an acronym not a word - you say the letters. Who's they? The only datbase vendor I've heard call their own product sequel is MS. klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] apparent loss of sys tables!! - help
On Thu, 16 Mar 2006 12:33:26 +1100, Noel Faux [EMAIL PROTECTED] wrote: Has anyone had this problem? while in psql: monashprotein= \d alignment ERROR: column c2.reltablespace does not exist monashprotein= \d region ERROR: column c2.reltablespace does not exist monashprotein= monashprotein= \d ERROR: relation pg_catalog.pg_roles does not exist Any pointers would be really appreciated! are you using an 8.x psql against a 7.x database? klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Putting restrictions on pg_dump?
On Thu, 5 Jan 2006 09:59:45 -0800, Benjamin Smith [EMAIL PROTECTED] wrote: Good ideas, all. but, what about keeping things like check constraints, foreign keys, etc? how about something like pg_dump -s -t customers dbname customers.def Hmmm... maybe, if I dumped the entire DB schema, with no data, and then looped thru the tables, creating a temp table (as you describe) with a funky name (such as TABLEaBcDeFgH_U) and then pg_dumping that, and then using a regex to rename the table in the output... (eg /TABLE\s+TABLEaBcDeFgH_U/TABLE customers/ Ugh. I was hoping there was a cleaner way... Make a script with all the commands in it. You should be able to manually make a file that is similar to what pg_dump does. pg_dump -s -t customers dbname customers.def echo copy customers from stdin; customers.def psql -d dname -c create temp table dump as select * from customers where id=11; copy dump to stdout; customers.def echo \. customers.def klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Putting restrictions on pg_dump?
On Wed, 4 Jan 2006 21:00:25 -0800, Benjamin Smith [EMAIL PROTECTED] wrote: Is there a way to put a limit on pg_dump, so that it doesn't dump ALL data, but that matching a particular query? Something like: pg_dump -da --attribute-inserts -t customers \ --matching-query=select * from customers where id=11; I'd like to selectively dump information from a query, but using the output format from pg_dump so that it can be used to create a (partial) database. Can this sort of thing be done? Not directly with pg_dump. You could create a table (create table customers_1 as select * from customers where id=11) and dump that but remember to change the tablename in the dump file or after loading it. You dont get any pk/fk/indexes on the table definition. You could also use copy to stdout/stdin. eg dump psql -d dbname -c create temp table dump as select * from customers where id=11; copy dump to stdout; dumpfile eg restore psql -d newdb -c copy customers from stdin dumpfile You might need to play around with supplying username/password. klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] dumping data to version control using pg_dump?
Is there any way to get pg_dump to run a statement before dumping? I'd like to do something like pg_dump -a -c create temp table params as select * from params where key=blah; -d dev_db -t params -f /svn/db/params.blah svn commit /svn/db/params.blah I'd rather avoid doing psql -c create table params_svn as select * from params where key = blah; pg_dump . psql -c drop table params_svn replace tmptablename realtablename svn commit ... klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
