[GENERAL] Last modification time of a database?
So, I've got loads of databases and I'd really like some way to see what the last actual modification time was for them. I can't just check the time stamp on the file in the database directory as autovacuum fudges that and trying to match up values for either last_autovacuum or last_autoanalyze in pg_stat_all_tables/indexes with the files' time stamps is no good as the time stamp on a file that was last written to by autovacuum is for when autovacuum stopped writing to it whereas the values in pg_stat_all_tables/indexes are from when autovacuum started the given operation and the difference between the two can vary with the size of the table. Am I missing something obvious here? If not, has anyone come up with a reliable way to do this? Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- 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_restore error - Any Idea?
On Mar 22, 2009, at 10:44 PM, DM wrote: Hi All, I am facing an error on executing the below command dump name: pg_dump_FcZ0.pnps_200903041201_1.2.1.0_base_testing databae name: pnqd_test $pg_restore -U postgres -p 5433 -d pnqd_test pg_dump_FcZ0.pnps_200903041201_1.2.1.0_base_testing pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3715; 0 0 ACL monitor postgres WARNING: errors ignored on restore: 1 I am not able to figure out this issue. Any idea guys. TOC - Table of Contents A dump made with pg_dump's -Fc will contain a table of contents of all of the database objects in the dump file. Something in that is causing an error for pg_restore. Does the version of pg_restore match up with the version of pg_dump that you used to make the dump? Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- 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] [SQL] bash postgres
On Mar 22, 2009, at 9:03 PM, Greenhorn wrote: Hi, I'm trying to pass variables on a bash script embedded with psql commands. cat header.txt to1,from1,subject1 to2,from2,subject2 to3,from3,subject3 to4,from4,subject4 cat b.sh #!/bin/bash two=2 psql -h localhost -U postgres -d mobile -c create temp table header ( field_1 textnot null, field_2 textnot null, field_3 textnot null ); \\copy header FROM header.txt CSV SELECT * FROM header limit $two; When I execute b.sh ERROR: syntax error at or near \ LINE 10: \copy header FROM header.txt CSV ^ How do I use \c (or any other psql commands beginning with a \) in a bash script? For multi-line input to a psql call in a bash (or any decent shell) script, I'd use a here document: #!/bin/bash #!/bin/bash two=2 psql -d pagila COPYTEST create temp table header ( field_1 textnot null, field_2 textnot null, field_3 textnot null ); \copy header FROM header.txt CSV SELECT * FROM header limit $two; COPYTEST $ ./tst.sh Null display is \N. Timing is on. CREATE TABLE Time: 7.568 ms Time: 2.374 ms field_1 | field_2 | field_3 -+-+-- to1 | from1 | subject1 to2 | from2 | subject2 (2 rows) Time: 1.011 ms (P.S. Your quotes around $two in your original are not needed, in fact they're straight up broken as $two is already inside of a double- quoted string). Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- 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] text column constraint, newbie question
You should use pg_query_params() rather than build a SQL statement in your code, to prevent SQL injection attacks. Also, if you are going to read this data back out and show it on a web page you probably should make sure there is no rogue HTML or JavaScript or anything in there with htmlentities() or somesuch. RebeccaJ wrote: Are there characters, maybe non-printing characters, or perhaps even whole phrases, that could cause problems in my database or application if I were to allow users to enter them into that column? If so, does anyone happen to have a regular expression handy that you think is a good choice for text columns' CHECK constraint? Or maybe a link to a discussion of this topic? Nope, there's nothing you can put into a text to break pgsql. However, if you are using regular old queries, you'd be advised to use pg_escape_string() function in php to prevent SQL injection attacks. Thanks! I'll check out pg_escape_string() in php, and I see that PostgreSQL also has something called PQescapeStringConn... I wonder if I should use both... Also, I should have asked: what about char and varchar fields? Can those also handle any characters, as long as I consider SQL injection attacks? -- 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] text column constraint, newbie question
On Mon, Mar 23, 2009 at 12:59 AM, Stephen Cook scli...@gmail.com wrote: You should use pg_query_params() rather than build a SQL statement in your code, to prevent SQL injection attacks. Also, if you are going to read this data back out and show it on a web page you probably should make sure there is no rogue HTML or JavaScript or anything in there with htmlentities() or somesuch. Are you saying pg_quer_params is MORE effective than pg_escape_string at deflecting SQL injection attacks? -- 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] text column constraint, newbie question
On Mon, Mar 23, 2009 at 3:07 AM, Scott Marlowe scott.marl...@gmail.com wrote: Are you saying pg_quer_params is MORE effective than pg_escape_string at deflecting SQL injection attacks? pg_query_params() will protect non-strings. For instance, read a number in from user input and do something of the form and foo=$my_number. Even if you escape the string, an attacker doesn't need a ' to close a string, so he can manage injection. If it's and foo=$1 using pg_query_params(), however, that's not possible. -- - David T. Wilson david.t.wil...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bash postgres
On Mon, 23 Mar 2009 15:03:15 +1100 Greenhorn user.postgre...@gmail.com wrote: Hi, I'm trying to pass variables on a bash script embedded with psql commands. cat header.txt to1,from1,subject1 to2,from2,subject2 to3,from3,subject3 to4,from4,subject4 cat b.sh #!/bin/bash two=2 psql -h localhost -U postgres -d mobile -c create temp table header ( I enjoy another technique that's not exactly embedding but makes the sql file easily executable from other shells to and easier to maintain (eg. you don't lose syntax highlight, you don't need to make wide use of x bit, you can concatenate files...). echo select :a; | psql --set a=3 test ?column? -- 3 (1 row) of course in spite of piping your sql, you could put it into a file. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] text column constraint, newbie question
On Mon, 23 Mar 2009 01:07:18 -0600 Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Mar 23, 2009 at 12:59 AM, Stephen Cook scli...@gmail.com wrote: You should use pg_query_params() rather than build a SQL statement in your code, to prevent SQL injection attacks. Also, if you are going to read this data back out and show it on a web page you probably should make sure there is no rogue HTML or JavaScript or anything in there with htmlentities() or somesuch. Are you saying pg_quer_params is MORE effective than pg_escape_string at deflecting SQL injection attacks? I didn't follow the thread from the beginning but I'd say yes. It should avoid queueing multiple statements and it is a more general method that let you pass parameters in one shot in spite of building the string a bit at a time for every parameter you insert (string, float, integer...). Of course if you correctly escape/cast/whatever everything injecting 2 statements shouldn't be possible... but if you don't you give more freedom to the attacker. $sql='select * from '.$table.' where a=$1 and $b=$2'; //oops I made a mistake. $result=db_query_params($sql,array(1,'bonzo')); If $table is external input and an attacker pass existingtable; delete from othertable; -- The attack may just result in a DOS if existingtable is there but your othertable shouldn't be wiped. untested... but I recall pg_query and pg_query_params use different C calls PGexec vs. PGexecParams and the later Unlike PQexec, PQexecParams allows at most one SQL command in the given string. http://www.postgresql.org/docs/8.3/interactive/libpq-exec.html I think pg_query_params should make a difference between floats and integers and signal an error if you pass float where integers are expected... but I'm not sure. Not really a security concern, but an early warning for some mistake. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] The tuple structure of a not-yet-assigned record is indeterminate.
M L wrote: CREATE VIEW tabelka AS SELECT someint FROM t_matches; What exactly are you trying to do here? If it worked how you've written it, you'd get the value of `someint' repeated once for each row that appears in t_matches. I don't know exactly why you're seeing the behaviour you are. However, the it works if you build the statement you want as a string and invoke it using EXECUTE: CREATE OR REPLACE FUNCTION add_view() RETURNS trigger AS $$ DECLARE someint integer; BEGIN EXECUTE 'CREATE VIEW tabelka AS SELECT '||NEW.id||' FROM t_matches;'; RETURN NULL; END; $$ language plpgsql; ... though the view produced isn't very useful. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] text column constraint, newbie question
On Mon, Mar 23, 2009 at 2:33 AM, Ivan Sergio Borgonovo m...@webthatworks.it wrote: On Mon, 23 Mar 2009 01:07:18 -0600 Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Mar 23, 2009 at 12:59 AM, Stephen Cook scli...@gmail.com wrote: You should use pg_query_params() rather than build a SQL statement in your code, to prevent SQL injection attacks. Also, if you are going to read this data back out and show it on a web page you probably should make sure there is no rogue HTML or JavaScript or anything in there with htmlentities() or somesuch. Are you saying pg_quer_params is MORE effective than pg_escape_string at deflecting SQL injection attacks? I didn't follow the thread from the beginning but I'd say yes. It should avoid queueing multiple statements and it is a more general method that let you pass parameters in one shot in spite of building the string a bit at a time for every parameter you insert (string, float, integer...). Of course if you correctly escape/cast/whatever everything injecting 2 statements shouldn't be possible... but if you don't you give more freedom to the attacker. $sql='select * from '.$table.' where a=$1 and $b=$2'; //oops I made a mistake. $result=db_query_params($sql,array(1,'bonzo')); If $table is external input and an attacker pass existingtable; delete from othertable; -- The attack may just result in a DOS if existingtable is there but your othertable shouldn't be wiped. untested... but I recall pg_query and pg_query_params use different C calls PGexec vs. PGexecParams and the later Unlike PQexec, PQexecParams allows at most one SQL command in the given string. http://www.postgresql.org/docs/8.3/interactive/libpq-exec.html I think pg_query_params should make a difference between floats and integers and signal an error if you pass float where integers are expected... but I'm not sure. Not really a security concern, but an early warning for some mistake. So, what are the performance implications? Do both methods get planned / perform the same on the db side? -- 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] Time intersect query
On Mar 23, 2009, at 5:44 AM, Brad Murray wrote: My current procedure... 1) Create temporary table with each possible data point. This example uses recursive functions from pgsql 8.4 but was originally implemented by using large numbers of queries from php. My knowledge of the recursive functions is pretty week, but I was able to put this together without too much trouble. create temp table timerange as with recursive f as ( select '2009-03-21 18:20:00'::timestamp as a union all select a+'30 seconds'::interval as a from f where a '2009-03-21 20:20:00'::timestamp ) select a from f; I think you can do that easier with the generate_series function, no need to use recursion that way. It's probably also convenient to have the end of the interval available. It would be something like: SELECT TIMESTAMP WITH TIME ZONE 'epoch' + f.a * INTERVAL '1 second' AS start, TIMESTAMP WITH TIME ZONE 'epoch' + f.a * INTERVAL '1 second' + INTERVAL '30 seconds' AS end FROM generate_series( EXTRACT(EPOCH FROM '2009-03-21 18:20:00'::timestamp)::bigint, EXTRACT(EPOCH FROM '2009-03-21 20:20:00'::timestamp)::bigint, 30 ) AS f(a) I get the impression you don't use this just once, so it may be better to keep the results (maybe with some added columns with derived values that can be used to join on easily) instead of creating a temp table. You could also add your mycount column here with some initialisation value (likely 0). I used something similar to generate a table that contained start and end dates of weeks based on week numbers and years. We had another table that contained periodic information, left joining the two tables it was easy to split the period table into a record per week with either the periodic information or NULL values (which meant no data for that week). I realise weeks per year aren't much data, but neither are your periods I think (although more). Having a scheduled script that would delete everything older than say a month would keep this table quite manageable (~90k records). 2) Update table with record counts alter table timerange add column mycount integer; explain analyze update timerange set mycount = (select count(*) from streamlogfoo where client = 3041 and a between startts(ts,viewtime) and ts); With the above you could join streamlogfoo and group by timerange.start, like so: SELECT timerange.start, COUNT(*) FROM timerange LEFT JOIN streamlog ON (streamlog.ts BETWEEN timerange.start AND timerange.end) GROUP BY timerange.start - This seems to work reasonably well, with the following exceptions... 1) The number reported is the number at the set time period, not the highest value between each data point. With a 30 second interval, this isn't a big problem, but with larger intervals gives results that do not match what I'm looking for (maximum users). 2) This does not scale well for large numbers of points, as internally each data point is a complete scan through the data, even though most of the data points will be common for the entire range. I'm thinking this would be a good use for the new window functions, but I'm not sure where to begin. Any ideas? Well, you'd need something to partition over, and since you don't know where your intervals start and end I don't see how you could do that without at least first generating your intervals. After that there doesn't seem to be much use for the windowing functions, as a simple group by seems to do what you want. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,49c75d4b129742009819935! -- 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] PostgreSQL versus MySQL for GPS Data
On March 20, I asked for help in the Newbie MySQL forum, got no answers. Then the forum administrator moved the post to the PostgreSQL MySQL forum -a forum that deals with PostgreSQL migration issues-, and again no answers. http://forums.mysql.com/read.php?83,253709,253709#msg-253709 Regards Juan Karlos 2009/3/20 Pavel Stehule pavel.steh...@gmail.com Hello it isn't correct comparation. MySQL people use mainly web forum regards Pavel Stehule 2009/3/20 Juan Pereira juankarlos.open...@gmail.com: John Cheng wrote: This is question for Juan, have you asked the MySQL mailing list? I'm afraid MySQL general list isn't as dynamic as PostgreSQL general list. http://lists.mysql.com/mysql/216795 MySQL general list: 4 answers in about 48 hours PostgreSQL general list: 27 answers in about 72 hours Thanks again to everybody for the amount of knowledge you have shared in this thread. Juan Karlos 2009/3/17 John Cheng chonger.ch...@gmail.com This is question for Juan, have you asked the MySQL mailing list? What do they say about this? On Tue, Mar 17, 2009 at 9:05 AM, Erik Jones ejo...@engineyard.com wrote: On Mar 17, 2009, at 4:47 AM, Craig Ringer wrote: The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? As you can imagine, PostgreSQL. My main reasons are that in a proper transactional environment (ie you're not using scary MyISAM tables) Pg is *much* better about handling concurrent load, particularly concurrent activity by readers and writers. Actually, following this comment it should be noted that if you were to choose MySQL you'd pretty much be making a decision to *not* be using transactions at all. The reason for this is that while InnoDB does support MySQL's geometry data types it does *not* support indexes on geometry columns, only MyISAM does which does not support transactions. Call me old fashioned if you like, but I like my data to have integrity ;) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- - John L Cheng
Re: [GENERAL] text column constraint, newbie question
On Mon, 23 Mar 2009 03:30:09 -0600 Scott Marlowe scott.marl...@gmail.com wrote: I think pg_query_params should make a difference between floats and integers and signal an error if you pass float where integers are expected... but I'm not sure. Not really a security concern, but an early warning for some mistake. So, what are the performance implications? Do both methods get planned / perform the same on the db side? I don't think there is any appreciable advantage. Maybe all the stuff ala fprint perform better in C rather than building up a string concatenating and escaping in php. Still I wouldn't consider it a first source of slowdown. For making a difference in plan management you've to use another family of functions pg_prepare/pg_execute. I'm not an expert but not every time caching plans is what you'd like to do. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] debugging in pgadmin
Hi all A lot of time since the last debugging activity. I don't remember how to debug. I thought I had to set a breaking point in the function i want to debug, and then call that function. I'm doing this, and from another query window, i call the function. But it returns me the result, but it doesn't stop inside the function in order to debug it. What am i doing wrong? thx Josep
Re: [GENERAL] Defer a functional index calculation?
On Fri, Mar 20, 2009 at 05:37:33PM -0700, Randall Lucas wrote: I added a functional index. create table example (id serial primary key, stuff text, parent_id int); create index example_root_idx on example (get_root_id(id)); (get_root_id(id) pulls an example row and recurses onto parent_id until it hits a root) I don't think you can do that; are you lying in the function's definition that it's immutable? As far as I know, and a quick check of the manual[1] seems to confirm it, all functions used in functional indexes must be immutable and the execution of an immutable function can't depend on any data in the database. If you're creating this function as immutable then this is lying to the database and it's guaranteed to go wrong at some point (as you seem to be finding out). What about having some trigger to cache the entry's root parent_id in another column? -- Sam http://samason.me.uk/ [1] http://www.postgresql.org/docs/current/static/sql-createindex.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 'create conversion' problem
Hello!
There is a database in KOI8-R encoding. And we have a client who is
querying the database:
set client_encoding TO 'ALT'
and then he write some data into the database.
I have a problem with some symbols which exists in ALT encoding and
which are absent in KOI8-R encoding. As result, during inserting strings
with such symbols postgresql returns an error, for example:
ERROR: character 0xfc of encoding ALT has no equivalent in MULE_INTERNAL
I've decided to write my own converter, so here is my code:
[code]
#include postgres.h
#include fmgr.h
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(fix_alt_to_koi8r);
PG_FUNCTION_INFO_V1(fix_koi8r_to_alt);
Datum
fix_alt_to_koi8r(PG_FUNCTION_ARGS)
{
elog(NOTICE,ALT_TO_KOI8);
unsigned char *src = PG_GETARG_CSTRING(*2*);
unsigned char *dest = PG_GETARG_CSTRING(*3*);
int len = PG_GETARG_INT32(*4*);
Assert(PG_GETARG_INT32(*0*) == PG_ALT);
Assert(PG_GETARG_INT32(*1*) == PG_KOI8R);
Assert(len = *0*);
static const unsigned char convert866toKOI8[] = {
*0*,*1*,*2*,*3*,*4*,*5*,*6*,*7*,*8*,*9*,*10*,*11*,*12*,*13*,*14*,*15*,
*16*,*17*,*18*,*19*,*20*,*21*,*22*,*23*,*24*,*25*,*26*,*27*,*28*,*29*,*30*,*31*,
*32*,*33*,*34*,*35*,*36*,*37*,*38*,*39*,*40*,*41*,*42*,*43*,*44*,*45*,*46*,*47*,
*48*,*49*,*50*,*51*,*52*,*53*,*54*,*55*,*56*,*57*,*58*,*59*,*60*,*61*,*62*,*63*,
*64*,*65*,*66*,*67*,*68*,*69*,*70*,*71*,*72*,*73*,*74*,*75*,*76*,*77*,*78*,*79*,
*80*,*81*,*82*,*83*,*84*,*85*,*86*,*87*,*88*,*89*,*90*,*91*,*92*,*93*,*94*,*95*,
*96*,*97*,*98*,*99*,*100*,*101*,*102*,*103*,*104*,*105*,*106*,*107*,*108*,*109*,*110*,*111*,
*112*,*113*,*114*,*115*,*116*,*117*,*118*,*119*,*120*,*121*,*122*,*123*,*124*,*125*,*126*,*127*,
*225*,*226*,*247*,*231*,*228*,*229*,*246*,*250*,*233*,*234*,*235*,*236*,*237*,*238*,*239*,*240*,
*242*,*243*,*244*,*245*,*230*,*232*,*227*,*254*,*251*,*253*,*255*,*249*,*248*,*252*,*224*,*241*,
*193*,*194*,*215*,*199*,*196*,*197*,*214*,*218*,*201*,*202*,*203*,*204*,*205*,*206*,*207*,*208*,
*210*,*211*,*212*,*213*,*198*,*200*,*195*,*222*,*219*,*221*,*223*,*217*,*216*,*220*,*192*,*209*,
*180*,*164*,*183*,*167*,*196*,*197*,*198*,*199*,*200*,*201*,*202*,*203*,*204*,*205*,*206*,*207*,
*208*,*209*,*210*,*211*,*212*,*213*,*214*,*215*,*216*,*217*,*218*,*219*,*220*,*221*,*222*,*223*,
*210*,*211*,*212*,*213*,*198*,*200*,*195*,*222*,*219*,*221*,*223*,*217*,*216*,*220*,*192*,*209*,
*179*,*241*,*242*,*243*,*244*,*245*,*246*,*247*,*248*,*249*,*250*,*251*,*252*,*253*,*254*,*255*
};
while(len){
*dest = convert866toKOI8[(unsigned char)(*src)];
++src;
++dest;
--len;
}
PG_RETURN_VOID();
}
Datum
fix_koi8r_to_alt(PG_FUNCTION_ARGS)
{
elog(NOTICE,KOI8_TO_ALT);
unsigned char *src = PG_GETARG_CSTRING(*2*);
unsigned char *dest = PG_GETARG_CSTRING(*3*);
int len = PG_GETARG_INT32(*4*);
Assert(PG_GETARG_INT32(*0*) == PG_KOI8R);
Assert(PG_GETARG_INT32(*1*) == PG_ALT);
Assert(len = *0*);
static const unsigned char KOI8to866[] = {
*0*,*1*,*2*,*3*,*4*,*5*,*6*,*7*,*8*,*9*,*10*,*11*,*12*,*13*,*14*,*15*,
*16*,*17*,*18*,*19*,*20*,*21*,*22*,*23*,*24*,*25*,*26*,*27*,*28*,*29*,*30*,*31*,
*32*,*33*,*34*,*35*,*36*,*37*,*38*,*39*,*40*,*41*,*42*,*43*,*44*,*45*,*46*,*47*,
*48*,*49*,*50*,*51*,*52*,*53*,*54*,*55*,*56*,*57*,*58*,*59*,*60*,*61*,*62*,*63*,
*64*,*65*,*66*,*67*,*68*,*69*,*70*,*71*,*72*,*73*,*74*,*75*,*76*,*77*,*78*,*79*,
*80*,*81*,*82*,*83*,*84*,*85*,*86*,*87*,*88*,*89*,*90*,*91*,*92*,*93*,*94*,*95*,
*96*,*97*,*98*,*99*,*100*,*101*,*102*,*103*,*104*,*105*,*106*,*107*,*108*,*109*,*110*,*111*,
*112*,*113*,*114*,*115*,*116*,*117*,*118*,*119*,*120*,*121*,*122*,*123*,*124*,*125*,*126*,*255*,
*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,
*32*,*32*,*32*,*32*,*32*,*249*,*32*,*32*,*32*,*32*,*32*,*32*,*248*,*32*,*32*,*32*,
*32*,*32*,*32*,*241*,*193*,*32*,*105*,*195*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*244*,
*32*,*32*,*32*,*240*,*192*,*32*,*73*,*194*,*252*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,
*238*,*160*,*161*,*230*,*164*,*165*,*228*,*163*,*229*,*168*,*169*,*170*,*171*,*172*,*173*,*174*,
*175*,*239*,*224*,*225*,*226*,*227*,*166*,*162*,*236*,*235*,*167*,*232*,*237*,*233*,*231*,*234*,
*158*,*128*,*129*,*150*,*132*,*133*,*148*,*131*,*149*,*136*,*137*,*138*,*139*,*140*,*141*,*142*,
Re: [GENERAL] text column constraint, newbie question
On Mon, Mar 23, 2009 at 03:30:09AM -0600, Scott Marlowe wrote: On Mon, Mar 23, 2009 at 2:33 AM, Ivan Sergio Borgonovo m...@webthatworks.it wrote: On Mon, 23 Mar 2009 01:07:18 -0600 Scott Marlowe scott.marl...@gmail.com wrote: Are you saying pg_quer_params is MORE effective than pg_escape_string at deflecting SQL injection attacks? I didn't follow the thread from the beginning but I'd say yes. It should avoid queueing multiple statements and it is a more general method that let you pass parameters in one shot in spite of building the string a bit at a time for every parameter you insert (string, float, integer...). Of course if you correctly escape/cast/whatever everything injecting 2 statements shouldn't be possible... but if you don't you give more freedom to the attacker. So, what are the performance implications? Do both methods get planned / perform the same on the db side? Isn't the main point that it's just easier to get things right if you use something that ends up calling PGexecParams under the hood rather than doing your own string interpolation? The frequency of SQL injection attacks[1,2,3,4] of people who really should know better suggests that we're (i.e. developers en masse) not very reliable at doing things properly and hence APIs that default to safety are a good thing. It's always easy as a developer to say oops, didn't think about that when you're debugging, but if that oops has just resulted in the compromise of details of a hundred thousand credit-cards then it becomes a somewhat more serious issue. Of course there are reasons for doing things differently, it's just that those should be special cases (i.e. performance hacks) and not the norm. Admittedly, using something like PGexecParams is a more awkward; but there are efforts to get decent string interpolation libraries going that just work. For example, the caja project has developed something they call Secure String Interpolation[5] which looks very neat and tidy. It would be cool if things like this appeared in other languages. -- Sam http://samason.me.uk/ [1] http://www.theregister.co.uk/2009/03/09/telegraph_hack_attack/ [2] http://www.theregister.co.uk/2009/02/27/lottery_website_security_probed/ [3] http://www.theregister.co.uk/2009/02/16/bitdefender_website_breach/ [4] http://www.theregister.co.uk/2009/02/13/f_secure_hack_attack/ [5] http://google-caja.googlecode.com/svn/changes/mikesamuel/string-interpolation-29-Jan-2008/trunk/src/js/com/google/caja/interp/index.html -- 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] debugging in pgadmin
--- On Mon, 23/3/09, josep porres jmpor...@gmail.com wrote: A lot of time since the last debugging activity. I don't remember how to debug. I thought I had to set a breaking point in the function i want to debug, and then call that function. I'm doing this, and from another query window, i call the function. But it returns me the result, but it doesn't stop inside the function in order to debug it. What am i doing wrong? Perhaps you've not got the pldebugger contrib module installed and run the sql script to create the debug functions? -- 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] debugging in pgadmin
mmm... my database schema have the pldbg functions. 2009/3/23 Glyn Astill glynast...@yahoo.co.uk --- On Mon, 23/3/09, josep porres jmpor...@gmail.com wrote: A lot of time since the last debugging activity. I don't remember how to debug. I thought I had to set a breaking point in the function i want to debug, and then call that function. I'm doing this, and from another query window, i call the function. But it returns me the result, but it doesn't stop inside the function in order to debug it. What am i doing wrong? Perhaps you've not got the pldebugger contrib module installed and run the sql script to create the debug functions?
Re: [GENERAL] debugging in pgadmin
On Mon, Mar 23, 2009 at 12:40 PM, josep porres jmpor...@gmail.com wrote: mmm... my database schema have the pldbg functions. Do you have something like this in your postgresql.conf: shared_preload_libraries = '$libdir/plugins/plugin_debugger.so' ? If not, add it, and restart the server (if you're on Windows, use plugin_debugger.dll) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] debugging in pgadmin
yes, i have: shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'# (change requires restart) 2009/3/23 Dave Page dp...@pgadmin.org On Mon, Mar 23, 2009 at 12:40 PM, josep porres jmpor...@gmail.com wrote: mmm... my database schema have the pldbg functions. Do you have something like this in your postgresql.conf: shared_preload_libraries = '$libdir/plugins/plugin_debugger.so' ? If not, add it, and restart the server (if you're on Windows, use plugin_debugger.dll) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Re: [GENERAL] debugging in pgadmin
it is Postgres Plus 8.3 Postgres 8.3.4 build 1400 2009/3/23 josep porres jmpor...@gmail.com yes, i have: shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'# (change requires restart) 2009/3/23 Dave Page dp...@pgadmin.org On Mon, Mar 23, 2009 at 12:40 PM, josep porres jmpor...@gmail.com wrote: mmm... my database schema have the pldbg functions. Do you have something like this in your postgresql.conf: shared_preload_libraries = '$libdir/plugins/plugin_debugger.so' ? If not, add it, and restart the server (if you're on Windows, use plugin_debugger.dll) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Re: [GENERAL] debugging in pgadmin
On Mon, Mar 23, 2009 at 12:50 PM, josep porres jmpor...@gmail.com wrote: yes, i have: shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll' # (change requires restart) Whats the output from: select * from pldbg_get_proxy_info(); -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] debugging in pgadmin
serverversionstr,serverversionnum,proxyapiver,serverprocessid PostgreSQL 8.3devel on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special),80300,3,4220 2009/3/23 Dave Page dp...@pgadmin.org On Mon, Mar 23, 2009 at 12:50 PM, josep porres jmpor...@gmail.com wrote: yes, i have: shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'# (change requires restart) Whats the output from: select * from pldbg_get_proxy_info(); -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Re: [GENERAL] debugging in pgadmin
On Mon, Mar 23, 2009 at 1:07 PM, josep porres jmpor...@gmail.com wrote: serverversionstr,serverversionnum,proxyapiver,serverprocessid PostgreSQL 8.3devel on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special),80300,3,4220 That should work, despite the mismatch in build envs (ignore the devel bit - that just tells us what version of Postgres the plugin was compiled against). What happens if you right-click a function in pgAdmin and select the Debug option (not the Set Breakpoint option)? If that doesn't work, can you please set the pgAdmin log level to debug, retry the test and then send the log over? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] LISTEN/NOTIFY problem
Hi everybody,
I've got a weird problem with LISTEN/NOTIFY.
My C++ app subscribes for the notifications, just like in libpq examples:
http://www.postgresql.org/docs/8.3/static/libpq-example.html
The only difference, that I am setting the timeout on select just to check
if application wants to exit.
//open session
//subscribe
while(!exit)
{
sock = PGsocket(conn)
res = select(sock);
if (res)
//check if it timeout
//or if there was a notification
}
The problem that I am facing is that after some time notifications stop
coming.
select() returns on timeout and nothing else.
Just as a test a included UNLISTEN/LISTEN sequence into the loop, so after
timeout or event application re-subscribes. And this helps.
The question is, what I am doing wrong with the code in the first place?
Any help is appreciated.
Cheers,
Dmitri.
--
@Gmail
Re: [GENERAL] debugging in pgadmin
On Mon, Mar 23, 2009 at 1:41 PM, josep porres jmpor...@gmail.com wrote: that way I can debug =) OK, so in that case can I get a log of an attempt to set a global breakpoint please? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] debugging in pgadmin
in the previous action, first of all I made a breakpoint and called the function from a query window. nothing happened, the only thing i got: the result. All actions are in the previous log. Is it what you want? 2009/3/23 Dave Page dp...@pgadmin.org On Mon, Mar 23, 2009 at 1:41 PM, josep porres jmpor...@gmail.com wrote: that way I can debug =) OK, so in that case can I get a log of an attempt to set a global breakpoint please? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Re: [GENERAL] debugging in pgadmin
On Mon, Mar 23, 2009 at 1:56 PM, josep porres jmpor...@gmail.com wrote: well, now a log with only trying to debug setting a breakpoint Hmmm - do you still have the demo schema on that server? Can you try setting a breakpoint on the list_emp() function, and then calling it please? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [SQL] bash postgres
Erik Jones ejo...@engineyard.com writes: On Mar 22, 2009, at 9:03 PM, Greenhorn wrote: How do I use \c (or any other psql commands beginning with a \) in a bash script? For multi-line input to a psql call in a bash (or any decent shell) script, I'd use a here document: Or echo/cat the script into psql's stdin, if you prefer that type of notation. The reason you have to do this is that psql doesn't recognize backslash commands in a -c string. There's a school of thought that doesn't want us to allow multiple commands in a -c string, even. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problem with at proramn
I wrote a program, several hundred lines long so I am not posting it. One of the things I must display is an interval. Workng through it one item at a time, all was well until I added the inteval ecpg type. Now it refuses to execute telling me it cannot find libpgtypes.so.3. I ran ldd agains the executable and go the following output: jer...@tux2:~/adminconsole/trunk/bin ldd editcourse.cgi libecpg.so.6 = /usr/local/lib/libecpg.so.6 (0x40017000) libstdc++.so.6 = /usr/local/lib/libstdc++.so.6 (0x400e5000) libm.so.6 = /lib/libm.so.6 (0x401d9000) libgcc_s.so.1 = /usr/local/lib/libgcc_s.so.1 (0x401fc000) libc.so.6 = /lib/libc.so.6 (0x40208000) libpgtypes.so.3 = not found libpgtypes.so.3 = /usr/local/pgsql/lib/libpgtypes.so.3 (0x4033) libpq.so.5 = /usr/local/pgsql/lib/libpq.so.5 (0x4033e000) /lib/ld-linux.so.2 = /lib/ld-linux.so.2 (0x4000) libcrypt.so.1 = /lib/libcrypt.so.1 (0x40357000) jer...@tux2:~/adminconsole/trunk/bin Note the two lines that begin libpgtypes.so.3. The first says it can find libpgtypes.so.3 the second points to it perfectly. Any idea what is happening here? -- View this message in context: http://www.nabble.com/problem-with-at-proramn-tp22661564p22661564.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] text column constraint, newbie question
On Mon, Mar 23, 2009 at 01:07:18AM -0600, Scott Marlowe wrote: On Mon, Mar 23, 2009 at 12:59 AM, Stephen Cook scli...@gmail.com wrote: You should use pg_query_params() rather than build a SQL statement in your code, to prevent SQL injection attacks. Also, if you are going to read this data back out and show it on a web page you probably should make sure there is no rogue HTML or JavaScript or anything in there with htmlentities() or somesuch. Are you saying pg_quer_params is MORE effective than pg_escape_string at deflecting SQL injection attacks? Yes. Much more. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore error - Any Idea?
Eric, There was no change in the version, we are using postgres v8.3.5 Thanks Deepak On Sun, Mar 22, 2009 at 11:17 PM, Erik Jones ejo...@engineyard.com wrote: On Mar 22, 2009, at 10:44 PM, DM wrote: Hi All, I am facing an error on executing the below command dump name: pg_dump_FcZ0.pnps_200903041201_1.2.1.0_base_testing databae name: pnqd_test $pg_restore -U postgres -p 5433 -d pnqd_test pg_dump_FcZ0.pnps_200903041201_1.2.1.0_base_testing pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3715; 0 0 ACL monitor postgres WARNING: errors ignored on restore: 1 I am not able to figure out this issue. Any idea guys. TOC - Table of Contents A dump made with pg_dump's -Fc will contain a table of contents of all of the database objects in the dump file. Something in that is causing an error for pg_restore. Does the version of pg_restore match up with the version of pg_dump that you used to make the dump? Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
[GENERAL] Garbage Collecting
I'm a postgresql newbie that's inherited eight production servers running Postgresql 8.2.5 as the backend. I have many questions covering topics such as administration of the database (upgrading, maintaining conf files, etc), improving the schema of the system (many tables don't currently have primary keys; to do anything useful you must join at least 5 tables), optimizing poor performing queries that can take hours, and knowing where functionality of the system should reside (curenly as PL/SQL functions, as external c code, external php code, and external perl code). Please refer me to appropriate documentation/FAQs/books. I've read http://www.postgresql.org/docs/faqs.FAQ.html If anyone knows of writeups for newbies that touches upon the things I mentioned, that would probably be really helpful for me. I have one specific question about Garbage Collecting within the database. The database system I'm working with has data that is no longer needed after a period of time. For example: transaction records only need to be kept around for the last 31 days; php web sessions that don't need to persist longer than a day. Could I create some function in the database that would act a bit like a daily cron job that deletes old records from tables (and then performs the appropriate VACUUM to regain the space)? If yes, how does one impliment something like that? As a trigger function written in PL/SQL? Can I hook the function into something that executes once per day? If no, why? Should the external scripts/code that puts the data into the database be responsible for removing the old data? Thanks in advance for any/all pointers! -Joshua -- Joshua Berry Software Engineer Opentech, S.A. +(595 21) 282557 Work +(595) 981 330 701 Mobile
Re: [GENERAL] (odbc) multiple step ole db generated error - date/timestamp column
ok i brought it in as varchar and cast as date. On Sun, Mar 22, 2009 at 12:27 PM, zach cruise zachc1...@gmail.com wrote: On Sun, Mar 22, 2009 at 3:39 AM, Craig Ringer cr...@postnewspapers.com.au wrote: zach cruise wrote: when importing from oracle 10g Importing how? CSV dump and load? DB link of some sort? odbc (see email) specifically Microsoft OLE DB Provider for Oracle Operating system and version? Oracle version? windows 2k3 oracle 10g (see email) postgresql 8.1 (see email) i get multiple step ole db generated error. From what program ? Where? navicat (see email) again, i narrowed this down to a date/timestamp column that gets imported ok if imported as varchar. there is another date/timestamp column that gets imported error-free, and other tables also get imported ok. based on my narrowing-down, it appears less likely error is at os/odbc/postgresql/oracle level but more likely at database/table/column/row level (esp since all problem rows = '01-JAN-01' (never null) but that could be a false-alarm). -- 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] Garbage Collecting
Joshua Berry wrote: I'm a postgresql newbie that's inherited eight production servers running Postgresql 8.2.5 as the backend. I have many questions covering topics such as administration of the database (upgrading, maintaining conf files, etc), improving the schema of the system (many tables don't currently have primary keys; to do anything useful you must join at least 5 tables), optimizing poor performing queries that can take hours, and knowing where functionality of the system should reside (curenly as PL/SQL functions, as external c code, external php code, and external perl code). Indexing can be a win once you figure out which quires are run the most and what the common where clauses look like. Indexes can eat up allot of disk space and slow performance in other places. Its a double edge sword. http://www.postgresql.org/docs/8.2/interactive/sql-createindex.html The database I maintain has far more Reads by a factor of 10,000 times to inserts, updates, and deletes combine so having many indexes is a win in my case. This is the part of tuning the database to the load. http://wiki.postgresql.org/wiki/Performance_Optimization http://wiki.postgresql.org/wiki/Database_Administration_and_Maintenance Please refer me to appropriate documentation/FAQs/books. I've read http://www.postgresql.org/docs/faqs.FAQ.html If anyone knows of writeups for newbies that touches upon the things I mentioned, that would probably be really helpful for me. http://wiki.postgresql.org/wiki/Main_Page and the help files with comments has lots of helpful information. Just make sure that you are reading information related to the version of Postgresql you are running. http://www.postgresql.org/docs/8.2/interactive/index.html I have one specific question about Garbage Collecting within the database. The database system I'm working with has data that is no longer needed after a period of time. For example: transaction records only need to be kept around for the last 31 days; php web sessions that don't need to persist longer than a day. Could I create some function in the database that would act a bit like a daily cron job that deletes old records from tables (and then performs the appropriate VACUUM to regain the space)? Yes you can do this with pgAgent it comes with pgAdmin http://www.pgadmin.org/docs/1.4/pgagent.html http://www.pgadmin.org/ If the records have a date when added then a command like so can be scheduled Delete from Mytable where DateAdded = (Current_date - '31 day '::interval' )::date If yes, how does one impliment something like that? As a trigger function written in PL/SQL? Can I hook the function into something that executes once per day? Yes it can be written in PL/SQL function then have pgAgent call it at midnight or at sometime thats off peak load times. Also you want to make sure autovaccum is turned to your needs http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html this can have big impacts on performance. If no, why? Should the external scripts/code that puts the data into the database be responsible for removing the old data? It really does not matter. Thanks in advance for any/all pointers! -Joshua -- Joshua Berry Software Engineer Opentech, S.A. +(595 21) 282557 Work +(595) 981 330 701 Mobile -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Proper entry of polygon type data
Hello, I would like to use 'polygon' type data and am wondering about the entry format of the vertex coordinates. Are the coordinates of the polygon type to be entered one entry per polygon vertex, or one entry per polygon edge segment? For example: I have a triangle with vertex corners A, B, C. One entry per vertex format suggests INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Cx,Cy)) ); One entry per edge format suggests INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Bx,By),(Cx,Cy),(Cx,Cy),(Ax,Ay)) ); Which entry format is the correct one? If per vertex format is the correct one, do I need to 'close' the path by entering the first vertex again at the end of the list? ie: INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Cx,Cy),(Ax,Ay)) ); Thanks, Peter -- 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] [SQL] bash postgres
On Mar 23, 2009, at 7:05 AM, Tom Lane wrote: Erik Jones ejo...@engineyard.com writes: On Mar 22, 2009, at 9:03 PM, Greenhorn wrote: How do I use \c (or any other psql commands beginning with a \) in a bash script? For multi-line input to a psql call in a bash (or any decent shell) script, I'd use a here document: Or echo/cat the script into psql's stdin, if you prefer that type of notation. The reason you have to do this is that psql doesn't recognize backslash commands in a -c string. There's a school of thought that doesn't want us to allow multiple commands in a -c string, even. Hmm... Apparently it does recognize them as long as the backslash is the first character in the command string: $ psql -c '\d' postgres No relations found. $ psql -c ' \d' postgres ERROR: syntax error at or near \ LINE 1: \d ^ Is that expected behavior? Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- 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] The tuple structure of a not-yet-assigned record is indeterminate.
2009/3/23 Craig Ringer cr...@postnewspapers.com.au
M L wrote:
CREATE VIEW tabelka AS SELECT someint FROM t_matches;
What exactly are you trying to do here? If it worked how you've written
it, you'd get the value of `someint' repeated once for each row that
appears in t_matches.
I don't know exactly why you're seeing the behaviour you are. However,
the it works if you build the statement you want as a string and invoke
it using EXECUTE:
CREATE OR REPLACE FUNCTION add_view() RETURNS trigger AS $$
DECLARE
someint integer;
BEGIN
EXECUTE 'CREATE VIEW tabelka AS SELECT '||NEW.id||' FROM t_matches;';
RETURN NULL;
END;
$$ language plpgsql;
... though the view produced isn't very useful.
--
Craig Ringer
thx4help, it just proof of concept. Real view is:
CREATE OR REPLACE FUNCTION add_view() RETURNS trigger AS $$
DECLARE
someint integer;
BEGIN
RAISE NOTICE 'dodajesz nowa lige %', NEW.id;
someint := NEW.id;
RAISE NOTICE 'dodajesz nowa lige %', someint;
CREATE VIEW tabelka AS SELECT * FROM tabela(someint);
RETURN NULL;
END;
$$ language plpgsql;
Also I have function and new type:
CREATE TYPE tables AS (name varchar(20), games smallint, wins smallint,
draws smallint, losts smallint, goals smallint, connected smallint, points
smallint);
CREATE OR REPLACE FUNCTION tabela(int) RETURNS SETOF tables AS
$BODY$
DECLARE
r tables%rowtype;
i integer;
teams record;
BEGIN
FOR teams IN SELECT * FROM t_teams WHERE league_id=$1
LOOP
-- can do some processing here
--RAISE NOTICE 'wartosc teams.id %', teams.id;
SELECT teams.full_name, games(teams.id), wins(teams.id), draws(
teams.id), losts(teams.id),goals(teams.id) ,connected(teams.id) ,points(
teams.id) FROM t_teams WHERE league_id=$1 INTO r;
--RAISE NOTICE 'czy mamy jakies inne r %', r;
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END
$BODY$
LANGUAGE ’plpgsql’ ;
pg_field_name(resource result, int field_number);
And when I make query i get:
league=# INSERT INTO t_leagues (name) VALUES('3liga');
NOTICE: dodajesz nowa lige 45
NOTICE: dodajesz nowa lige 45
ERROR: there is no parameter $1
CONTEXT: SQL statement CREATE VIEW tabelka AS SELECT * FROM tabela( $1 )
PL/pgSQL function add_view line 7 at SQL statement
General purpose of this trigger is to create new table view whenever new
league is added. I think that problem is with FOR teams IN SELECT *
FROM t_teams WHERE league_id=$1 from function tabela(). Any ideas how to
cope with that? How should I create that kind of view?
Re: [GENERAL] [SQL] bash postgres
Erik Jones ejo...@engineyard.com writes: On Mar 23, 2009, at 7:05 AM, Tom Lane wrote: The reason you have to do this is that psql doesn't recognize backslash commands in a -c string. There's a school of thought that doesn't want us to allow multiple commands in a -c string, even. Hmm... Apparently it does recognize them as long as the backslash is the first character in the command string: Hmm, maybe I was just misremembering the details. What's certainly true is that psql doesn't run a -c string through the same extensive parsing that data from stdin gets. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] unexpected check constraint violation
Hi, Can someone explain why postgres complains in this case: create table t(d real, check(d=0.00603)); insert into t values (0.00603); ERROR: new row for relation t violates check constraint t_d_check thanks Jacek -- 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] unexpected check constraint violation
Jacek Becla wrote: create table t(d real, check(d=0.00603)); insert into t values (0.00603); ERROR: new row for relation t violates check constraint t_d_check Because equality is not well-defined for real values? - Jeremy -- 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] unexpected check constraint violation
On Mar 23, 2009, at 1:41 PM, Jeremy Harris wrote: Because equality is not well-defined for real values? That was my first thought, too, but why would two identical real literals evaluate to different bit patterns? -- 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] unexpected check constraint violation
On Mar 23, 2009, at 2:54 PM, Jacek Becla wrote: Hi, Can someone explain why postgres complains in this case: create table t(d real, check(d=0.00603)); insert into t values (0.00603); ERROR: new row for relation t violates check constraint t_d_check thanks Jacek try this: insert into t values (0.00603::real); Ries -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] libpq -- reading a timestamp with time zone using binary format
how does one determine whether libpq is sending an int64 or a double? I see all of the #ifdefs in the source: #ifdef HAVE_INT64_TIMESTAMP static int64 time2t(const int hour, const int min, const int sec, const fsec_t fsec); #else static double time2t(const int hour, const int min, const int sec, const fsec_t fsec); #endif but it is still unclear (at least to me) how to determine as the client whether the server has been compiled with the HAVE_INT64_TIMESTAMP flag. since both int64 and double use 8 bytes of storage on x86_64, you can't determine the type using PQfsize. Any suggestions would be appreciated. -Whit -- 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] unexpected check constraint violation
On Mon, Mar 23, 2009 at 1:54 PM, Jacek Becla be...@slac.stanford.edu wrote: Hi, Can someone explain why postgres complains in this case: create table t(d real, check(d=0.00603)); insert into t values (0.00603); ERROR: new row for relation t violates check constraint t_d_check Without any casting, 0.00603 likely evaluates to a numeric. select 0.00603::numeric 0.00603::real; ?column? -- t So, this works: create table t(d real, check(d=0.00603::real)); insert into t values (0.00603); INSERT 0 1 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 1- and 2-dimensional indexes on same column: why is the 2d one preferred?
Recent versions of PostgreSQL seem to prefer 2d indexes somehow: for a table foo with i_a btree (a) i_ab btree (a, b) SELECT * FROM foo WHERE a=123 will often use i_ab and not i_a (even right after ANALYZE). This raises some questions: - is there even any benefit in still having both these indexes? (can some operations still use i_a only or is i_ab always a sufficient replacement for i_a?) - is this even working as intended? in my experience (can't back it up with numbers atm.), 2-dimensional indexes are often slower and they degrade noticeably over time. Without knowing the implementation, I'd assume that using i_ab would usually require more page fetches than using i_a for the above query. Regards, Marinos -- 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] LISTEN/NOTIFY problem
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org]on Behalf Of Dmitri Girski
Sent: Monday, 23 March 2009 10:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] LISTEN/NOTIFY problem
Hi everybody,
I've got a weird problem with LISTEN/NOTIFY.
My C++ app subscribes for the notifications, just like in libpq examples:
http://www.postgresql.org/docs/8.3/static/libpq-example.html
The only difference, that I am setting the timeout on select just to check if
application wants to exit.
//open session
//subscribe
while(!exit)
{
sock = PGsocket(conn)
res = select(sock);
if (res)
//check if it timeout
//or if there was a notification
}
The problem that I am facing is that after some time notifications stop coming.
select() returns on timeout and nothing else.
Just as a test a included UNLISTEN/LISTEN sequence into the loop, so after
timeout or event application re-subscribes. And this helps.
The question is, what I am doing wrong with the code in the first place?
Any help is appreciated.
Cheers,
Dmitri.
--
@Gmail
Cannot tell what is wrong with you piece of code.
But below is a routine I use for notifications.
No problems with it so far.
Hope it helps.
Allan
/*
** Structure to hold the connection data
*/
typedef struct _condetails
{
char *pghost;
char *pgport;
char *pgoptions;
char *pgtty;
char *dbname;
char *pguser;
char *pgpswd;
PGconn *conn;
int bpid;
} CONDETAILS;
/*
** Register for a database notification
*/
int reg_notification( CONDETAILS *cd, const char *notif )
{
char *sqlnotify, sql[128];
PGconn* conn;
PGresult* res;
conn = cd-conn;
sqlnotify = listen %s;
/*
** check to see that the backend connection was successfully made
*/
if ( PQstatus( conn ) == CONNECTION_BAD )
{
sysErr( reg_notification(). %s, PQerrorMessage( conn ) );
PQfinish( conn );
condetails( cd );
return -1;
}
/*
** Register
*/
sprintf( sql, sqlnotify, notif );
res = PQexec( conn, sql );
if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
{
sysErr( reg_notification(). listen command failed. %s,
PQerrorMessage( conn ) );
PQclear( res );
return -1;
}
PQclear( res );
return 0;
}
/*
** Wait for a database notification or time out
** This is an async method.
**
** Return only the first notification not generated by me
** and flush the rest.
**
** Probly should do something smarter.
*/
int wait_db_notification( CONDETAILS *cd, char *notif, const long sec, const
long usec )
{
int sock, ret;
fd_set input_mask;
PGconn* conn;
PGnotify *notify;
struct timeval timeout;
conn = cd-conn;
/*
** check to see that the backend connection was successfully made
*/
if ( PQstatus( conn ) == CONNECTION_BAD )
{
sysErr( wait_db_notification(). %s, PQerrorMessage( conn ) );
PQfinish( conn );
condetails( cd );
return -1;
}
sock = PQsocket( conn );
if ( sock 0 )
{
sysErr( wait_db_notification(). Could not get socket
descriptor. %s, PQerrorMessage( conn ) );
return -1;
}
FD_ZERO( input_mask );
FD_SET( sock, input_mask );
timeout.tv_sec = sec;
timeout.tv_usec = usec;
ret = select( sock + 1, input_mask, NULL, NULL, timeout );
if ( ret 0 )
{
sysErr( %d: %s, __LINE__, wait_db_notification(). Select on
db socket failed );
if ( errno != EINTR )
exit( 1 );
/*
** Been interrupted by a trappable signal
** Force going to the top of the loop to handle it.
-*/
return -1;
}
if ( ret == 0 )
{
/*
** Time out occurred.
*/
return 0;
}
/*
** Some activity on the db
*/
ret = -1;
PQconsumeInput( conn );
do
{
notify = PQnotifies( conn );
if ( notify != NULL )
{
if ( cd-bpid != notify-be_pid )
{
strcpy( notif, notify-relname );
ret = 1;
Re: [GENERAL] text column constraint, newbie question
On Mar 22, 12:36 pm, scott.marl...@gmail.com (Scott Marlowe) wrote: ayup. As long as they're legal for your encoding, they'll go right in. If you wanna stuff in anything no matter the encoding, use a database initialized for SQL_ASCII encoding. Thanks, everyone, for your contribution to this thread. I'm approaching the database design of my web application differently, now. Before, I was planning to have CHECK constraints in all of my text or char fields, to keep out all semicolons, single quotes, and anything else that looked dangerous. Now I'm thinking that I'll be using htmlentities(), pg_escape_string() and pg_query_params() as safety filters, and otherwise allowing users to store whatever they want to, in the fields where I store/retrieve user input. Scott, your comment above introduced some new concepts to me, and now I'm thinking about foreign language text and other ways to be more flexible. I found this page that talks about encoding: http://www.postgresql.org/docs/8.3/static/multibyte.html And I wonder why you like SQL_ASCII better than UTF8, and whether others have any opinions about those two. (My web server's LC_CTYPE is C, so I can use any character set.) Wouldn't UTF8 allow more characters than SQL_ASCII? Thanks again! -- 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] unexpected check constraint violation
Thanks Ries. Do you know if that is a postgres feature or a bug? In practice, I wanted to load the data from a file using COPY FROM. Modifying a large csv file in impractical and not very elegant. thanks, Jacek ries van Twisk wrote: On Mar 23, 2009, at 2:54 PM, Jacek Becla wrote: Hi, Can someone explain why postgres complains in this case: create table t(d real, check(d=0.00603)); insert into t values (0.00603); ERROR: new row for relation t violates check constraint t_d_check thanks Jacek try this: insert into t values (0.00603::real); Ries -- 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] unexpected check constraint violation
On Mon, Mar 23, 2009 at 2:52 PM, Jacek Becla be...@slac.stanford.edu wrote: Thanks Ries. Do you know if that is a postgres feature or a bug? It's not a bug, it's lack of precision in the definition on your part being interpreted by pgsql. When you create the table, you get this: create table t(d real, check(d=0.00603)); \d t Table public.t Column | Type | Modifiers +--+--- d | real | Check constraints: t_d_check CHECK (d = 0.00603::double precision) Note that having not been told the type for the check constraint, pgsql defaults to double precision. So, in effect, your table creation was this: create table t(d real, check(d=0.00603::double precision)); You can either cast the check constraint, or change the field type to match double precision. create table t(d double precision, check(d=0.00603::double precision)); create table t(d real, check(d=0.00603::real)); Either of those will work properly. -- 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] text column constraint, newbie question
On Mon, Mar 23, 2009 at 3:11 PM, RebeccaJ rebec...@gmail.com wrote: Scott, your comment above introduced some new concepts to me, and now I'm thinking about foreign language text and other ways to be more flexible. I found this page that talks about encoding: http://www.postgresql.org/docs/8.3/static/multibyte.html And I wonder why you like SQL_ASCII better than UTF8, and whether others have any opinions about those two. (My web server's LC_CTYPE is C, so I can use any character set.) Wouldn't UTF8 allow more characters than SQL_ASCII? No, SQL_ASCII will allow anything you wanna put into the database to go in, with no checking. UTF8 will require properly formed and valud UTF characters. Which is better depends a lot on what you're doing. Note that SQL_ASCII is not 8 bit ASCII, it's a name for anything goes instead. (Now Cole Porter is running through my head.. :) ) -- 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] PostgreSQL versus MySQL for GPS Data
On Monday 23. March 2009, Juan Pereira wrote: On March 20, I asked for help in the Newbie MySQL forum, got no answers. Then the forum administrator moved the post to the PostgreSQL MySQL forum -a forum that deals with PostgreSQL migration issues-, and again no answers. This kind of supports my suspicion that people who use MySQL either haven't heard of PostgreSQL or are too dumb to understand the difference. /troll -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Determining/Setting a server's time zone
Hi, How/Where does PostgreSQL set or determine the local time zone? On my server, I am seeing (+00): db= SELECT now(); now --- 2009-03-23 22:32:47.595491+00 (1 row) But on my workstation I am seeing (-04): db= SELECT now(); now --- 2009-03-23 18:16:36.591653-04 (1 row) The only thing I can think of is that my server is set to believe the BIOS time is UTC and my workstation is set to EDT. Does PostgreSQL check this from the host machine? For reference, my server is Debian Sarge (4.0) and my workstation is Ubuntu 8.10. Thanks! Madi -- 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] text column constraint, newbie question
On Mon, 23 Mar 2009 14:11:28 -0700 (PDT) RebeccaJ rebec...@gmail.com wrote: now. Before, I was planning to have CHECK constraints in all of my text or char fields, to keep out all semicolons, single quotes, and anything else that looked dangerous. Now I'm thinking that I'll be using htmlentities(), pg_escape_string() and pg_query_params() as check, htmlentities, pg_escape_string and pg_query_params really don't belong to the same family of functions and serve very different purposes. simplifying it very much: - check are used to control the quality of data that get stored in the db - htmlentities is about formatting for web output - pg_escape_string is to prepare input for sql and avoiding sql injection - pg_query_params is a relative of pg_escape_string but somehow used differently -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] unexpected check constraint violation
Scott Marlowe scott.marl...@gmail.com writes: You can either cast the check constraint, or change the field type to match double precision. The short answer here is that 0.00603::double precision and 0.00603::real are unlikely to be exactly the same value, and which one is greater is a matter of which direction the real got rounded off in. On my machine the former is a bit larger: regression=# select 0.00603::double precision - 0.00603::real; ?column? -- 1.85072421797494e-10 (1 row) but on another platform it could be the other way around. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libpq -- reading a timestamp with time zone using binary format
Whit Armstrong armstrong.w...@gmail.com writes: but it is still unclear (at least to me) how to determine as the client whether the server has been compiled with the HAVE_INT64_TIMESTAMP flag. You look at the integer_datetimes parameter. You could execute a SQL SHOW command, but in a libpq client it's sufficient to use PQparameterStatus(), because this value is sent automatically during connection startup. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libpq -- reading a timestamp with time zone using binary format
Thanks, Tom. That's just what I needed. -Whit On Mon, Mar 23, 2009 at 7:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Whit Armstrong armstrong.w...@gmail.com writes: but it is still unclear (at least to me) how to determine as the client whether the server has been compiled with the HAVE_INT64_TIMESTAMP flag. You look at the integer_datetimes parameter. You could execute a SQL SHOW command, but in a libpq client it's sufficient to use PQparameterStatus(), because this value is sent automatically during connection startup. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 1- and 2-dimensional indexes on same column: why is the 2d one preferred?
Marinos Yannikos m...@geizhals.at writes: Recent versions of PostgreSQL seem to prefer 2d indexes somehow: for a table foo with i_a btree (a) i_ab btree (a, b) SELECT * FROM foo WHERE a=123 will often use i_ab and not i_a (even right after ANALYZE). I suspect that these indexes are exactly the same size --- look at pg_class.relpages or use the pg_relation_size() function to verify. If they are, the computed access cost will be exactly the same and which one gets picked is an implementation artifact. (I think that in the current code the one that has the larger OID gets picked, but that's not something I'd suggest you rely on.) It wouldn't really matter anyway because the actual runtime should be pretty much the same too. The most likely reason for this to happen is that you're talking about two int4 columns and you're on a 64-bit machine that is going to align index entries to 8-byte boundaries. The one-column index isn't actually any smaller because of alignment padding :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Determining/Setting a server's time zone
Madison Kelly li...@alteeve.com writes: How/Where does PostgreSQL set or determine the local time zone? Well, show timezone will tell you what PG is using. Where it came from is a bit harder to answer. The default is to use whatever zone is current according to the postmaster's startup environment, and that would depend on some factors you didn't tell us, like how you're starting the postmaster. Do your two machines report the same timezone when you run date as a shell command? The easy solution is to set the value you want in postgresql.conf. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] text column constraint, newbie question
On Mar 23, 2009, at 10:11 PM, RebeccaJ wrote: On Mar 22, 12:36 pm, scott.marl...@gmail.com (Scott Marlowe) wrote: ayup. As long as they're legal for your encoding, they'll go right in. If you wanna stuff in anything no matter the encoding, use a database initialized for SQL_ASCII encoding. Thanks, everyone, for your contribution to this thread. I'm approaching the database design of my web application differently, now. Before, I was planning to have CHECK constraints in all of my text or char fields, to keep out all semicolons, single quotes, and anything else that looked dangerous. Now I'm thinking that I'll be using htmlentities(), pg_escape_string() and pg_query_params() as safety filters, and otherwise allowing users to store whatever they want to, in the fields where I store/retrieve user input. Yes indeed. But don't use together: * use pg_escape_string() or pg_query_params() to escape data that goes INTO your database, and * use htmlentities() on data that comes OUT of it, and only once it gets printed to the page. Otherwise you'll end up with data in your database that is hard to search in (there is no collation on html entities in text fields AFAIK, so if someone searches for é in your forms it doesn't match eacute; in your database) or data in your scripts that is hard to compare (the value from a GET or POST request does not contain entities while the value read and converted from the database does). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,49c81dc9129743370417724! -- 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] text column constraint, newbie question
RebeccaJ wrote: And I wonder why you like SQL_ASCII better than UTF8, and whether others have any opinions about those two. (My web server's LC_CTYPE is C, so I can use any character set.) Wouldn't UTF8 allow more characters than SQL_ASCII? I've had a LOT of experience dealing with apps that use 8-bit byte strings (like SQL_ASCII `text') to store data, and I've rarely seen one that *doesn't* have text encoding handling bugs. If you store your text as byte streams that don't know, check, or enforce their own encoding you must keep track of the encoding separately - either with another value stored alongside the string, or through your app logic. If you start storing data with multiple different text encodings in the DB, you're most likely to land up tracking down annoying corrupt text bugs sooner or later. If, on the other hand, you use UTF-8, you *know* that everything in the database is well-formed UTF-8. You ensure that it is UTF-8 before storing it in the DB and know it'll be UTF-8 coming out. The DB takes care of encoding conversion for you if you ask it to, by setting client_encoding - the only downside being that it'll refuse to return strings that can't be represented in your current client_encoding, like say Cyrillic (Russian etc) text if you're using ISO-8859-1 (latin-1) for your client encoding. Even with a UTF-8 database you must still get your I/O to/from libraries and the rest of the system right, converting UTF-8 text to whatever the system expects or vice versa. Alternately, if you set client_encoding, you must be prepared for cases where the DB can't send you what you ask for because your encoding can't represent it. All in all, I personally think a UTF-8 database is considerably better for most uses. There are certainly cases where I'd use SQL_ASCII, but not most. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Last modification time of a database?
Erik Jones wrote: Am I missing something obvious here? If not, has anyone come up with a reliable way to do this? Triggers on all your tables that append to a logging table? Have the client do it? Note that you do *NOT* want to have triggers that attempt to UPDATE a table to record the last modified time for that table. They'll cause transactions that touch the same table to block waiting until the first one commits/rolls back, so they'll ruin your concurrency. They may also cause unexpected deadlock aborts of transactions. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Determining/Setting a server's time zone
Tom Lane wrote: Madison Kelly li...@alteeve.com writes: How/Where does PostgreSQL set or determine the local time zone? Well, show timezone will tell you what PG is using. Where it came from is a bit harder to answer. The default is to use whatever zone is current according to the postmaster's startup environment, and that would depend on some factors you didn't tell us, like how you're starting the postmaster. Do your two machines report the same timezone when you run date as a shell command? The easy solution is to set the value you want in postgresql.conf. regards, tom lane Hi Tom, 'date' shows the same: Server (PostgreSQL 8.1): $ date Mon Mar 23 20:07:20 EDT 2009 db= show timezone; TimeZone -- GMT (1 row) Workstation (PostgreSQL 8.3): $ date Mon Mar 23 20:07:09 EDT 2009 db= show timezone; TimeZone --- localtime (1 row) Neither has the environment variable 'TZ' set (at least, 'echo $TZ' returns nothing). Also, 'cat /etc/postgresql/8.1/main/environment' has no values on either machine. In both cases, the postmaster is started by init.d. The only reference to time zone I could otherwise find was in the 'postgresql.conf' file. Both are commented out with the comment that timezone defaults to TZ. My concern with forcing a value in the postgresql.conf file is forgetting to update the conf file when EDT/EST changes... Thanks for the help so far! Madi -- 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] Determining/Setting a server's time zone
On Mon, Mar 23, 2009 at 6:14 PM, Madison Kelly li...@alteeve.com wrote: Tom Lane wrote: Madison Kelly li...@alteeve.com writes: How/Where does PostgreSQL set or determine the local time zone? Well, show timezone will tell you what PG is using. Where it came from is a bit harder to answer. The default is to use whatever zone is current according to the postmaster's startup environment, and that would depend on some factors you didn't tell us, like how you're starting the postmaster. Do your two machines report the same timezone when you run date as a shell command? The easy solution is to set the value you want in postgresql.conf. regards, tom lane Hi Tom, 'date' shows the same: Server (PostgreSQL 8.1): $ date Mon Mar 23 20:07:20 EDT 2009 db= show timezone; TimeZone -- GMT (1 row) Workstation (PostgreSQL 8.3): $ date Mon Mar 23 20:07:09 EDT 2009 db= show timezone; TimeZone --- localtime (1 row) Neither has the environment variable 'TZ' set (at least, 'echo $TZ' returns nothing). Also, 'cat /etc/postgresql/8.1/main/environment' has no values on either machine. In both cases, the postmaster is started by init.d. The only reference to time zone I could otherwise find was in the 'postgresql.conf' file. Both are commented out with the comment that timezone defaults to TZ. My concern with forcing a value in the postgresql.conf file is forgetting to update the conf file when EDT/EST changes... As long as you pick a timezone that has is_dst set to true in the pg_timezone_names table you'll be ok. -- 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] Determining/Setting a server's time zone
Madison Kelly li...@alteeve.com writes: Server (PostgreSQL 8.1): $ date Mon Mar 23 20:07:20 EDT 2009 db= show timezone; TimeZone -- GMT (1 row) Hmm. Apparently, this machine is configured so that TZ is set properly in the environment of user login processes (perhaps in /etc/profile?) but stuff launched from init sees TZ unset or set to GMT. Messing with the init environment might break some other program that wants it to be GMT, so changing postgresql.conf is the best answer. My concern with forcing a value in the postgresql.conf file is forgetting to update the conf file when EDT/EST changes... As Scott said, you select a zone specification that is DST-aware; you don't have to change it every six months, or indeed ever unless you move. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Single missing WAL in long sequence..
Hi, I ran into a recovery problem where I have a single missing WAL file in a long sequence. I need a way to recover past that missing WAL archive. I am desperately hoping there is a way to do this. Any help that can be given will be extremely appreciated! Thanks! Aimon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
