[ADMIN] time zone on timestamp fields
Ok me dumb, I've spend copple days editing my table definintions from Dlm DATETIME NOT NULL DEFAULT TEXT 'now' Dlm = date time last modified to Dlm timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP which from my previous post I was told should be Dlm timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP now some where along the line I expect to get asked what time zone, and I don't want my stupid programs to have to me time zone aware, Back in the good ol days I could use someting like cst5cdt, but I don't think that will work any more. and since the postgres programers who I am grateful to, don't want to code this politicly controled stuff either, their documentation reflects this. and realy this makes no difference to me either except in one one in october from 2:00 am to 2:00 am. But before I go back and change the 100 so table defenitions again, and change the ones I haven't made it to yet could someone please give me a quick and dirty explaination as to what I should change it to, and If you want to elaberate on why, I'll consider it a plus. Thanks in advance Arno ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] fyi
previous table definition guess no not null of default '' curreent table definitionCREATE TABLE apps ( Id int not null unique,-- AppId Sym varchar(8) not null default '', -- app symbol name Nam varchar(30) not null default '',-- app full name BitPriv varchar(10) not null default '',-- app bit priv NamPriv varchar(255) not null default '', -- app nam priv AppDir varchar(80) not null default '',-- app dir nam AppPag varchar(32),-- app page nam Aka int,-- ptr to real name Uid int not null default 0, -- ptr to las mod user id Dlm timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE apps ( Id int not null unique,-- AppId Sym varchar(8) not null default '', -- app symbol name Nam varchar(30) not null default '',-- app full name BitPriv varchar(10) not null default '',-- app bit priv NamPriv varchar(255) not null default '', -- app nam priv AppDir varchar(80) not null default '',-- app dir nam AppPag varchar(32) not null default '',-- app page nam Aka int,-- ptr to real name Uid int not null default 0, -- ptr to las mod user id Dlm timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ); query on pqsql postgresql7-7.0.2.k62-8 redhat 7.2 select * from apps where (not (apppag = null or apppage = '')); returns data not sure if current table actualy has not null default '' on apppag field but I know that was the reason for the query being as it was query on pgsql postgresql 7.3.2-3 redhat 9.0 with new table definition select * from apps where (not (apppag = null or apppag = '')) returns nothing 0 rows select * from apps where (not apppag = null); returns nothing when it should return every thing select * from apps where (not apppag = ''); returns data as expected Lessons learned as programer always check database definitions only check for what is possible. (not my style I like to write paranoid code) (never trust the dba) lessens learned as a dba always prevent as much illeagle, unwanted data as possible. (I try when ever I get the enough time to spend on being a dba) and finialy the programer and the dba should talk often to optimize code espesialy when there the same person. Arno ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] fyi
Stephan Szabo wrote: > > On Thu, 31 Jul 2003, maillist wrote: > > > select * from apps where (not apppag = null); > > returns nothing when it should return every thing > > No. It should return nothing. apppag = null returns unknown for every > row. Use IS NULL to test if a value is null. hmmm now i'm confused when i set a value i don't use the phrase to null and '= null' has always worked in the past, and if '= null' doesn't work it should be an invalid query because i can see no other use for it, and for the most part is, to are ignored anyways. so would the correct query be select * from apps where (not apppag = is null) or select * from apps where (not apppag is null) both of these look silly, but I do use null in other fields on purpuse to represent uninitailized fields. So I definitlay am going to have to grep my code for "=*null" and check to see what needs to be done. Ijust thought it was a bug, that should not have been found, because the query had un needed checks. I need to do more testing before I allow production servers to upgrade Arno ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] fyi regarding error I've seen posted before
Database error:Invalid SQL: VACUUM VERBOSE ANALYZE ids_logs PostgreSQL Error: 1 (ERROR: simple_heap_delete: tuple concurrently updated ) aged deleted 28954 records I got these error messages from my php script that processes system logs from my linux / unix boxes. this is the first time I've seen this error message, but since people have asked about it before I thought I'd addd my two cents. after I process all my text log files into the database I click on the age button which does a series of updates update pri to pri -1 for anything older than a day update pri to pri -1 for any thing older than a week update pri to pri -1 for anthing older than a month after I do all the updates I run a delete query to delete all records with pri < -99 after I do that I run VACUUM VERBOSE ANALYZE ids_logs and this is where the errors showed up reruning the aging process just ages and deleted more records, so I'm assuming it was a one time thing, nothing to worry about and since the data is trasient, and used to see if my systems are running ok deleteing and recreating the table is no big deal. I'll just lose all the people / ips that have hacked / scaned my boxes in the last month since I've read about this error before on the list, and I know that there were no other processes accessing the table I thought this info might be of value Arno ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] fyi regarding error I've seen posted before
Tom Lane wrote: > > maillist <[EMAIL PROTECTED]> writes: > > Database error:Invalid SQL: VACUUM VERBOSE ANALYZE ids_logs > > PostgreSQL Error: 1 (ERROR: simple_heap_delete: tuple concurrently > > updated ) > > Is it possible that you had another VACUUM ANALYZE running concurrently > on this same table? > > regards, tom lane > it is possible that the mouse dbl clicked on the age button, if so i didn't notice it. but when i analize the logs tomorow morning i'll know for sure, because all my pgsql servers use syslog. and what i was running is the tool i wrote in php to process those logs Arno ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] fyi error follow up
there was a cron job running doing a vacum on the entire database ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] fyi error follow up
Tom Lane wrote: > > maillist <[EMAIL PROTECTED]> writes: > > there was a cron job running doing a vacum on the entire database > > I was about to say that then it's a known issue --- two ANALYZE > operations running concurrently for the same table can generate > a "simple_heap_insert" or "simple_heap_update" failure from trying > to update the same pg_statistic row at the same time. This is fairly > harmless (as long as one of 'em succeeds, who cares if the other > doesn't?). > > However, looking back at your original message, you said the error > mentioned "simple_heap_delete", didn't you? Hmm ... how old is your > PG installation? ANALYZE hasn't used simple_heap_delete since 7.1... > > regards, tom lane well it may be really old to you but in my world its middle of the road I have pgsql 7.0.3 running on sco open server 7.0.2 running on redhat 6.2 7.1.3-4bp.2 running on rh 7.2 (where problem occured) 7.3.2-3 running on redhat 9.0 (where I'm finding all the wiz bang improvments that are breaking my code) whatever version runs on redhat as 2.1 replacing 7.0.3, allowing me to retire 7.0.2 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] pg_hba.conf ident sameuser
can this use the encrpted responses from pidentd? Tried search of the archives, but this wasn't helpful thanks Arno ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Call to undefined function: pg_connect()
shreedhar wrote: > > Hi All, > > I Updated my Postgresql Server from 7.2.4 which came along with RH7.3 to > 7.3.2. After updating I could not able to connect database through PHP. So I > recompiled PHP4.1.2 (same version which have earlier in my system), even > then I am not able to connect through PHP. Is there any other package has to > be installed for this connection. > > Please some body help me out regarding. I am breaking my head since two > days. > > Thanks for any idea. > > Regards, > Sreedhar Bhaskararaju > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend the only time I have received that error message is when /usr/lib/php4/pgsql.so was not installed on rh 9.0 installation of server + kde + gnome. seems they didn't install php-pgsql. to verify that your new php actualy has postgresql configured create test file test.php containing run php -f test.php > test.html view test.html with browser and verify that pgsql.so made it into the /usr/lib/php4 directory hope this helps, I know my gogle search didn't, but the other rh 9.0 install did work and the only thing i did different was to check install every thing hope this helps ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] System Tables
well in unix man psql command line options -E shows the queries that are generated by the \d or other sudo commands reading a 10 point font on a 1600x1280 screen sucks, please text only to mailing lists -- Arno Karner aka Behind the Muffs West 7th St., St. Paul aka smell, Minn http://tnss.com/noise_stink ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[ADMIN] multibytes language support
Hi, there, I installed postgresql 7.3.4 by RPMs, and created a database. Now I found I need multibytes language support. How can I change the database setting? I don't want to re-create the database. I installed JDBC and when some columns have multibytes chars. JDBC throws error. Please help me for that. Kevin Yang ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Fatal error: Allowed memory size of 8388608 bytes exhausted
shreedhar wrote: > > I am using PHP4 as client side. > [snip] > Subject: Re: [ADMIN] Fatal error: Allowed memory size of 8388608 bytes > exhausted > this is a php appache message. either A) you have a phpscript that allocates memory in a infinate loop B) your php script has become so big, or its data has become so big to exceed the default 8 meg provided by apache php.ini, or apache php.ini php_value memory_limit 16M on apache 2.x I wasn't able to use the M, I had to use 1600 I usualy try google searchs on error message before I assume they are one pice of software or another, you are currently using 3 apache, php, postgresql -- Arno Karner aka Behind the Muffs West 7th St., St. Paul aka smell, Minn http://tnss.com/noise_stink ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
