[GENERAL] Postgres 8.2 database recovery Could not create relation Invalid Argument
Novice here :). I have PostgreSQL 8.2 installed on a single board computer running Windows XP Embedded on a Compact Flash drive - 2 databases with no more than 2000 ro. After 10 power cycles spaced 6 mins apart, I noticed the postgres.exe processes no longer running. I located log file (see below) and it appears postgres attempts to recover but fails to do so. Any suggestions: 2009-07-28 04:31:01 LOG: database system was interrupted at 2009-07-28 04:27:54 GMT Daylight Time 2009-07-28 04:31:01 LOG: checkpoint record is at 0/7CBF58 2009-07-28 04:31:01 LOG: redo record is at 0/7CBF58; undo record is at 0/0; shutdown TRUE 2009-07-28 04:31:01 LOG: next transaction ID: 0/12290; next OID: 17183 2009-07-28 04:31:01 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2009-07-28 04:31:01 LOG: database system was not properly shut down; automatic recovery in progress 2009-07-28 04:31:02 LOG: redo starts at 0/7CBFA8 2009-07-28 04:31:02 FATAL: could not create relation 1663/16403/16586: Invalid argument 2009-07-28 04:31:02 CONTEXT: xlog redo update: rel 1663/16403/16586; tid 35/32; new 35/33 2009-07-28 04:31:02 LOG: startup process (PID 1484) exited with exit code 1 2009-07-28 04:31:02 LOG: aborting startup due to startup process failure 2009-07-28 04:31:02 LOG: logger shutting down Thanks in advance for any insights. Jus
Re: [GENERAL] Best practices for moving UTF8 databases
Phoenix Kiula wrote:
I tried this. Get an error.
mypg=# select * from interesting WHERE NOT description ~ ( '^('||
mypg(#$$[\09\0A\0D\x20-\x7E]|$$|| -- ASCII
mypg(#$$[\xC2-\xDF][\x80-\xBF]|$$|| -- non-overlong 2-byte
mypg(# $$\xE0[\xA0-\xBF][\x80-\xBF]|$$||-- excluding overlongs
mypg(#$$[\xE1-\xEC\xEE\xEF][\x80-\xBF]{2}|$$|| -- straight 3-byte
mypg(# $$\xED[\x80-\x9F][\x80-\xBF]|$$||-- excluding surrogates
mypg(# $$\xF0[\x90-\xBF][\x80-\xBF]{2}|$$|| -- planes 1-3
mypg(#$$[\xF1-\xF3][\x80-\xBF]{3}|$$|| -- planes 4-15
mypg(# $$\xF4[\x80-\x8F][\x80-\xBF]{2}$$|| -- plane 16
mypg(# '*)$' )
mypg-#
mypg-# ;
ERROR: invalid regular expression: quantifier operand invalid
If you really don't want to go the pg_dump - iconv (remove invalid
characters) - diff the dump files route, a stored procedure that
searches for invalid characters was posted a few years back that
attempts to find the invalid characters.
http://archives.postgresql.org/pgsql-hackers/2005-12/msg00511.php
http://svana.org/kleptog/pgsql/utf8_verify.sql
--
Justin Pasher
--
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] Documentation - PgAdmin
over the last 3 years i can't recall it being included in the msi installer Now the MSI installers from Enterprise DB is a One Click installer i'm not sure whats all included. I have it installed on one or 2 machines but never really dug into what's all included to tell you what all in the new installer. but the the pgAdmin installer from here http://www.postgresql.org/ftp/pgadmin3/release/v1.10.0/win32/ As far I can think back this installer never included CHM. I found it annoying but given this supports multiple versions of PG which CHM file does one include. Message from mailto:db.subscripti...@shepherdhill.biz at 07-05-2009 07:14:49 AM -- Quoting justin jus...@emproshunts.com: CHM has not been packaged for as long as i know of with PgAdmin.. Justin, Are you sure? Windows help file CHM has been the default help file that pgAdmin opens when you click on the help icon on the toolbar of pgAdmin (uptill Postgresql's version 8.3.7) using MSI installer. Or are you saying the MSI package maintainers included the CHM files and not pgAdmin packagers? If that is the case, I should direct my question to EnterpriseDB who is the new windows binary maintainer. Regards, Chris
Re: [GENERAL] Pls help
Scott Marlowe wrote: On Fri, Jul 3, 2009 at 10:02 PM, Roseller A. Romanosdon2_...@yahoo.com wrote: Note that this could be a permissions problem. All the files need to belong to whatever user postgres runs as. If the destination PG install successfully started before there will be no permission problems. When copying files from one computer to another security credentials do not follow in Windows, security will be inherited from parent directory by default. The only time security in windows has that kind of problem is when moving the physical hard drive to another computer. This creates orphaned UUID describing security credentials in meta data of the NTFS volume To clean up that kind of security mess requires first taking ownership of the files then reseting all the permissions with replace option. -- 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] Documentation - PgAdmin
db.subscripti...@shepherdhill.biz wrote: Hi, Congratulations on the release of version 8.4. I am surprised that pgAdmin's help link is now directed to the documentation website of postgresql. The embedded help CHM was not packaged with it. This implies that anytime one needs help on a simple syntax, one must connect to the internet. Was this by design or omission? Regards, Chris CHM has not been packaged for as long as i know of with PgAdmin.. http://www.postgresql.org/docs/manuals/ I'm not sure about Enterprise DB package as it includes pgAdmin and may have CHM file along for the ride
Re: [GENERAL] 64 Bit ODBC Drivers for windows
use the .net provider http://npgsql.projects.postgresql.org/ I'm not sure of a 64bit build. although a 32bit version should run on 64 bit windows without any problems. Message from mailto:drewtimm...@gmail.com Andrew Timmins drewtimm...@gmail.com at 06-29-2009 05:36:58 PM -- Is there anyway to connect to postgre using a 64 bit Windows OS? I am having problems connecting to a local DB since i have upgraded my computer. I would like to connect using C#.NET Any help would be appreciated. Drew
Re: [GENERAL] Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function
APseudoUtopia wrote: thread, then logs out (intending to read all the other forum threads at some point in the future when they log in again). If I used a VIEW, it would automatically consider all those unread forum posts to be read when the user logs out. That wouldn't work. What if a user logs in, reads only one forum You are keeping a list of all the forums a user has read, i would not worry about making sure the table tracking user activity has duplicate key values. The select can be limited to return just on row with the highest time stamp then compare this result to figure out what forms the user has not read yet. This eliminates one of problems but creates a problem where table tracking user activity is going bloat but in low traffic times delete the duplicate values. A similar topic was discussed on the performance mailing list, where updates are hung for several seconds for a similar tracking table... http://archives.postgresql.org/pgsql-performance/2009-06/msg00300.php
Re: [GENERAL] Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function
APseudoUtopia wrote:
Hey list,
I have a query which allows users to "Catch up" on read posts on the
forum. It works by either updating or inserting the "last post read"
number from every forum thread into the readposts table (for that
userid and threadid combination, of course). Here's the table
structure:
Wouldn't a view be better than having a table that is deleted and
updated all the time. I would add a field in the user table called
last_login type timestamp then do a select from the forums table to
generate this table where last_login = FormTimeStamp .
I don't see the point having this table when a view would work better.
Obviously this will not work with PostgreSQL. I've googled around a
bit and I decided to create a plpgsql function to handle the task. I
don't have much done, but here's what I have:
-
CREATE FUNCTION FORUM_CATCH_UP_ALL (INTEGER) RETURNS VOID AS
$FuncTag$
BEGIN
LOOP
-- Try to update the record
-- This query is broken. I'm not sure how to do the subquery or
whatever I need to do. Maybe FROM? Another loop?
-- UPDATE "forums_readposts" SET "lastpostread" = (SELECT
"lastpost" FROM "forums_topics" WHERE blah blah
IF found THEN
RETURN;
END IF;
-- Not there, try to insert the key
-- If someone else inserts the same key concurrently
-- We could get a unique-key failure
BEGIN
INSERT INTO "forums_readposts" ("userid", "threadid",
"lastpostread") (SELECT $1, "id", "lastpost" FROM "forums_topics")
WHERE "userid" = $1;
RETURN;
EXCEPTION WHEN unique_violation THEN
-- Do nothing, and loop to try the update again
END;
END LOOP;
END;
$FuncTag$
LANGUAGE plpgsql;
-
if you want to do something like this either do a test first to see if
the key is present in the table, update or do an insert like this
There is no reason to do a loop in the function waiting for a lock to
clear. Postgresql Locks do not work like MySQL.
CREATE FUNCTION FORUM_CATCH_UP_ALL (pUserID INTEGER) RETURNS VOID AS
$FuncTag$
BEGIN
select lastpostread from forums_readposts where
userid = pUserId;
if ( found() ) then
UPDATE forums_readposts SET "lastpostread" =
(SELECT lastpost FROM forums_topics WHERE blah blah) ; --its helpful to
post the entire function ;
else
INSERT INTO forums_readposts ( userid,
threadid,
lastpostread) (SELECT $1, id,
lastpost FROM forums_topics)
WHERE userid = pUserID;
end;
END;
$FuncTag$
LANGUAGE plpgsql;
I got the structure from the example in the postgresql documentation.
Hopefully it's a step in the right direction.
If anyone can point me in the direction to take another step in, I'd
really appreciate it.
Thanks.
Creating a view would work better and than creating a table to track
this. I would think this website tracks the last time the user logged
in correct??? This is going to create allot of over head maintaining
this table when a simple select statement will work so much better if i
understand what you are doing.
Re: Fwd: [GENERAL] Date math
Adam Rich wrote:
Guy Flaherty wrote:
You could use the extract() function to calculate the day of year of
the person's birthdate and then check if this number is within today's
day of year and range of days you want to check for, for example,
today's day of year + 30 days to be within a month. That way you don't
need to worry about years at all. You may need to double check this will
work on the leap years though!
Thanks! that's even better than what I just came up with:
birth_date + ((interval '1 year') * ceil(EXTRACT(DAYS FROM (now() -
birth_date))/365.25))
And I like the Day of year solution because (I think) I can use a
functional index on that value.
it kind of ugly looking but here is one that uses only math and no
problem with leap years or anything
select current_date, '07-02-1979'::date +
((date_part('year',current_date) - date_part( 'year',
'07-02-1979'::date))::text||'year')::interval
So the select statement might look like this
select birth_day
where birthday + ((date_part('year',current_date) - date_part(
'year',birth_day))::text||'year')::interval Between now() and now() +
'90 day'::interval
--
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] Please suggest me on my table design (indexes!)
DaNieL wrote: Hi guys, im tryin to optimize a simple table, suited for contain users. So, my table at the moment is: - CREATE TABLE contacts( id BIGSERIAL PRIMARY KEY NOT NULL UNIQUE, company_id BIGINT, code varchar(10), company_name varchar(120), name varchar(120), surname varchar(120), phone varchar(80), email varchar(80), kind varchar(8) ); - I use this layout in order to store 3 kind of users: Private, Company and Company's Employee.. the col 'kind' infact will contain just 'private', 'company' or 'employee', but is unnecessary, i can understand what kind a user is by those rules: Private are stand-alone users, company_name and company_id are always NULL; Company have the company_name; Employees have the company name and the company_id (with the id of the company's row); The layout looks find although i would not use Bigserial unless you expect to exceed 2.1 billion records Example: id|company_id|code| company_name|name|surname|phone| email | kind 1 |NULL |C001| Sunday Inc. | John | Doe |8 | j...@sunday.com | company 2 | 1 |E001| Sunday Inc. |Paul | Smith | 7| sm...@sunday.com| employee 3 | NULL|P001| NULL | Rose | Mary | 6| r...@mary.com | private So, first of all, does this layout looks good? Before i used to keep employees in a different table, becose usually employees have just few data (name, surname, direct email and direct phone.. all the addresses, bank data, etc.. belongs tot he company), but noe i preferred this way to avoid constant inner joins. Now, i aspect that my users will search the contact table just for the fields company_name, name, surname, email, code. That kind of query cant be as WHERE company_name = '$x' but will be much like WHERE company_name LIKE '$x%', both becose i use an autocomplete field for the quick search, both becose.. well, that's how users search data's (in my experience). What i have done with searches on small strings where the user is unsure what they are looking for or the spelling, I do something like this where substr(company_name,1,length($searchtext$ UserSearchString $searchtext$)) ilike $searchtext$UserSearchString $searchtext$ and mix it with soundex. This way the user get a list of possible matches with only handful to type characters Draw back is this type of search is it can't be indexed. So i created those index, to let the query planner use the indexes in the LIKE query: My doubt is: am i using too many indexes? Will my insert/delete/update queryes be too slow, and does the select optimization worth the price? (and, does this way really optimize the selects queryes?) The more indexes you have the slower updates will be. Yet not a horrible amount. The answer to this is it depends on the work load can the system suffer the overhead of the indexes and still give adequate results on queries. Consider that my application wont do many insert-delete-update sequentially. -- 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-8.3.7 unexpected connection closures
Craig Ringer wrote: On Fri, 2009-06-19 at 01:03 -0400, Tom Lane wrote: I see lots of questions here that seem to be related to (a) virus scanner interference and (b) installation/reinstallation. Lots of the reinstall issues seem to be with people who don't really understand NT users, ACLs, etc and aren't really competent to admin a machine, but they do make me wonder if the Pg installer can do more to help them out, eg: I'm hesitant to agree with this the PG installer doing more automatically. If the user does not understand security and proper configuration to get it to work on windows client, the server setup will be poorly configured with security problems that a MAC truck can drive through. Example is MS itself and CAD developers. I have yet to see a CAD program that does not require to run with Administrator security credentials. MS with many of its Server Programs does similar stuff now that PG does automatically creates users sets up the directory/registry security. Its really easy to trash those setting keeping the app from working. You hear about the same problems many people talk about hear as they do with the other databases running on windows see http://support.microsoft.com/kb/309422 http://support.microsoft.com/kb/287932 http://msdn.microsoft.com/en-us/library/ms175043.aspx The only thing i think that would make sense, is to have the installer add exceptions to the windows firewall for the Postgresql ports. "The data directory you have specified (C:\PgData) already exists, but cannot be accessed by the user you want to run PostgreSQL as. Would you like to: [a] use a new data directory, [b] grant the postgresql user the rights to access the data directory you have specified, or [c] change the user you start PostgreSQL as to the user owning the data directory ? [clean install to new directory][grant access to old directory][change postgresql user][cancel installation]" This is the same problem MSSQL has on reinstall except it just creates new directories and Security Credentials automatically. I have seen computers with 5 SQLServer user accounts. along with lots of other crap laying around PG install does lots of the stuff for the user but does not do everything as many installers do which can leave the OS install a complete and utter mess requiring reformat and reinstall to get it to work at all. Or leaves that applications install such tangle mess its impossible to figure out what the user did. I can read it know "Postgresql gurus i go to the PGDATA directory and find it has PGDATA though PGDATA_8 how do i tell which is the current data directory." I have suffered this question with MsSQL. All that is accomplish is changing the question asked. "The data directory you have specified (C:\PgData) contains a database from an older version of PostgreSQL (8.2) that this version (8.3.6) cannot access. Would you like to use a new data directory C:\PgData-8.3, leaving the old one untouched? Note that PostgreSQL will not automatically convert your data. You REALLY should read the upgrading documentation before continuing. [clean install to new data directory][cancel installation]" "PostgreSQL has detected that another program, probably an older version of PostgreSQL, is listening on port 5432. If you want to use this version on the default port 5432, you will need to stop or uninstall the other program first. [Change PostgreSQL port][Cancel installation]" I agree more verbose install messages on errors and warnings would be nice. Of course, if Windows development is un-fun, windows program installation and installer building is more so. That is very true -- Craig Ringer
Re: [GENERAL] postgresql-8.3.7 unexpected connection closures
Pavel Stehule wrote: 2009/6/19 Leif B. Kristensen l...@solumslekt.org: nobody needs Windows. But Postgres has only one reputation. Problems on windows are PostgreSQL's problem too. And there are some native windows firms that starts develop with Postgres. And this people expecting stability. So if we support some, we have to do it well. regards Pavel Stehul i've been running Postgresql on windows for a few years now and did not suffer these problems . I have dealt with these problems running MsSQL 7 and 2000. If any software is having a specific problem with windows I guarantee the MS offering is having the same problem. Just replace X application with the MS offering you will have possible list of solutions.
Re: [GENERAL] used for large media files
Steve Atkins wrote: On Jun 17, 2009, at 8:43 AM, Mike Kay wrote: Now that's an interesting way of doing this I never thought about before. Using a fileserver though, how would I categorize and index the files? I was planning on using multiple databases to hold the data - one for each client and a separate database for each file type. Yes, they would be hosted on the same server. I see the bottleneck. I suppose that instead of saving the files, indexes and categories all in the same database, I could simply reference the location and file names in the database - and index and categorize in this manner. Does this make sense? Storing all the metadata in the database and the content on the filesystem of the webserver lets both do what they're good at. Serving static files from the filesystem of the webserver is ridiculously cheap compared with retrieving the data from the database, as it's something that everything from the kernel up is optimized to do. Backups are much simpler too. Using the database to store BLOBs or do it via File system is a very old debate going back and fourth with common tone the db is slower the file system is faster. Using a DB easies maintenance, simplifies indexing, security and gives transaction protection to the files. In my view the only argument still holding water storing large binary files on the Filesystem vs the DB is the overhead/access time losses connecting and read data from DB. The file system just wins out yet has several draw backs. Also consider one does not need to use the large object interface anymore, the bytea type with TOAST simplify that problem . The draw back is you can't jump around the binary stream and the size is limited to 1Gig per record. One of the big draw backs to using File system and a DB for indexing/meta data is keeping the two up to date and linked. If files get accidentally deleted or moved to different directories the database index is now useless. This by itself can cause maintenance nightmare as the number of files and directories get into 10 of thousands. This also complicates disaster recovery because the directory structure has to be recreated exactly to get it to work again. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ruby connect
Hi, I'm trying to connect ruby to postgres on ubuntu and the only link I found that has the library is down. Does anyone have the postgres library for ruby? Or direct me to it?
Re: [GENERAL] Adding automatic backup of a DB
Moe wrote: Hi, How can I add automatic backup easily? Is it possible with pgadmin ? Also, I would like to know whether those options (Blobs(checked), OIDS, Insert Commands, Disable Quoting is for) .. as it is now, it works great without them. Thanks in advance / Moe Not with pgadmin but you can automate the back with pgdump and pgdumpall using scripts or batch files here is a set of instructions for windows http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows
Re: [GENERAL] Rounding problems
Paolo Saudin wrote: Hi, I have a problem with a query wich simple aggregate values. In the sample below I have two values, 1.3 and 1.4. Rounding their average with one decimals, should give 1.4. The first query with - cast( tables_seb.tbl_arvier_chamencon.id_1 AS numeric) AS value - give the expected result, while the second one with - tables_seb.tbl_arvier_chamencon.id_1 AS value - give 1.3. Which could be the reason ?? My first thought is whats with all the castings??? Castings are mostly likely the cause of your problems, What is tbl_arvier_chamencon.id_1 data type??? I'm guessing its something other than numeric. All other floating point data types will have problems caused by Binary Floating-Point Arithmetic Numeric data type uses different functions to do its math for the stated purpose of being exact yet being allot slower. In one query casting is done prior to avg() yet in the other casting is done after avg(). This will allow Postgres to use different functions to calculate average giving an unexpected result.
Re: [GENERAL] keeping track of function execution
Wojtek wrote: Hi, I have a question on transactions/isolation levels/etc... In my PL/pgSQL function main loop goes through inventory list of active devices, for each one executing processing applicable for given device, like: FOR i in --i is %rowtype select device_id as device_id, type as type from devices_list where active = 1 LOOP (...) -- here is CASE statement, checking value of 'type' parameter () END LOOP; --simple enough, right? This processing is pretty heavy and takes lot of time... so, I'd like to be able to monitor as processing progresses and I need to be able to say: -which devices've been processed already -which ones've not been processed yet My first idea was to create table, updated by my function each time next device is processed, like: device_id;status 1;0--done 2;0--done 3;1--processing is running 4;2--to be processed But... Postgress treats function as single transaction, of course. Hence, I'm not able to see any changes in my progress monitoring table until my main function is finished and all the statuses are set to 0. Which is not really the intent (again, the intent is to be able to monitor which devices are yet to be processed while function is still running!) My ideas so far (none is perfect, unfortunately) - move my loop to php/other external piece of code... so it will log-in progress in my function using separate transactions (well, I don't want to use external code, would prefer to stay in PL/pgSQL) - log to text file (slow and not easy to report later on) Can I ask for any other suggestions/comments? Is there a way I can have this functionality, please? Regards, foo What about using Triggers when a device is being processed throw a flag in anther table or in the same table. I'm guessing some other kind of process set the status 0, 1 or 2 before this slow process gets going. if that is the case just add a trigger on update to set the status. Then a simple query will get you what you want. If I'm understanding what your after.
Re: [GENERAL] Number Conversion Function
Tino Wildenhain wrote: justin wrote: I disagree the database is the wrong place, there are cases it makes sense. Which cases would that be? Regards Tino Report engines that don't have this ability . I use for check writing. To come think, I don't know of UI framework or report engine that has this ability. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Querying a Large Partitioned DB
: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Index Scan on systemevents_msg_idx_040609 (cost=0.00..1836.37 rows=24506 width=0) (actual time=92.079..92.079 rows=34loops=1) Index Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Heap Scan on systemevents_040709 systemevents (cost=1844.72..89127.11 rows=24790 width=152) (actual time=114.387..262.360 rows=24 loops=1) Recheck Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Index Scan on systemevents_msg_idx_040709 (cost=0.00..1838.52 rows=24790 width=0) (actual time=84.848..84.848 rows=24loops=1) Index Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Heap Scan on systemevents_032909 systemevents (cost=320.54..17254.18 rows=4841 width=142) (actual time=67.808..67.810 rows=1 loops=1) Recheck Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Index Scan on systemevents_msg_idx_032909 (cost=0.00..319.33 rows=4841 width=0) (actual time=56.044..56.044 rows=1 loops=1) Index Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Heap Scan on systemevents_033009 systemevents (cost=1556.24..75179.65 rows=20931 width=149) (actual time=77.644..335.360 rows=43 loops=1) Recheck Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Index Scan on systemevents_msg_idx_033009 (cost=0.00..1551.01 rows=20931 width=0) (actual time=72.454..72.454 rows=43loops=1) Index Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Heap Scan on systemevents_033109 systemevents (cost=1892.97..92637.60 rows=25806 width=149) (actual time=86.468..86.856 rows=4 loops=1) Recheck Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Index Scan on systemevents_msg_idx_033109 (cost=0.00..1886.52 rows=25806 width=0) (actual time=70.397..70.397 rows=4 loops=1) Index Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Heap Scan on systemevents_040109 systemevents (cost=1395.47..66260.67 rows=18430 width=149) (actual time=85.711..177.369 rows=12 loops=1) Recheck Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Index Scan on systemevents_msg_idx_040109 (cost=0.00..1390.86 rows=18430 width=0) (actual time=67.481..67.481 rows=12 loops=1) Index Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Heap Scan on systemevents_040209 systemevents (cost=1619.92..76738.75 rows=21326 width=150) (actual time=89.065..89.067 rows=1 loops=1) Recheck Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Index Scan on systemevents_msg_idx_040209 (cost=0.00..1614.58 rows=21326 width=0) (actual time=73.229..73.229 rows=1 loops=1) Index Cond: (message_index_col @@ '''funkju'''::tsquery) Total runtime: 93364.070 ms (49 rows) Can you give me any tips and suggestions about how to speed this up? It seems like a smart query planner would understand the rules and know that it should search in the last partitions first, since it is ordering by device reported time. Thanks! justin
Re: [GENERAL] Internationalization
Pedro Doria Meunier wrote: Hi all, I'm wondering how to internationalize contents of a table, short of having a column for each language string ... Anyone with some experience to share? :) Regards, Pedro Doria Meunier How about parent child table layout. The child table has one record for translation for each document. something like this Create table ParentDoc ( docid serial, description text ) Create table ChildDoc ( docid integer, doc_text text, short_description text, language text ) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible for PostgreSQL to support mmddyyyy date format?
This is from the link 1/8/1999 January 8 in MDY mode; August 1 in DMY mode 1/18/1999 January 18 in MDY mode; rejected in other modes 01/02/03 January 2, 2003 in MDY mode; February 1, 2003 in DMY mode; February 3, 2001 in YMD mode you can run this select '01/01/2009'::date, '01-01-2009'::date You can change the output for the client.. http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE Bernard Barton wrote:. I'm currently porting an Informix based application to PostgreSQL 8.3. All of the dates processed by the application are in the mmdd format. According to the date input table at the link below, the mmdd format is not listed. The mmdd format is supported, but I'd have to modify a LOT of code to change the format, which I'm trying to avoid. Is there any way to get PG to work with the mmdd date format? I know I can use the to_date function, but again, it would be MUCH simpler if PG worked with this format. -Thanks http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE
Re: [HACKERS] [GENERAL] string_to_array with empty input
Steve Crawford wrote: Did I miss the exciting conclusion or did this drift silently off radar? it was pretty well split between the options. tabled for another time. -- 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] Number Conversion Function
Tom Lane wrote: Tino Wildenhain t...@living-examples.com writes: I would not recommend to do this within the database. Thats typical a job for your presentation layer. ... but having said that, I think the "money" datatype has a function for this. Whether that's of any use to you I dunno; money is pretty restrictive about what it can handle. regards, tom lane I disagree the database is the wrong place, there are cases it makes sense. I have looked for what Tom talks about for the money type i can't find any thing?? so I wrote a function primary purpose is used with checks but with a little modification will work for anyone one and has no practical limitation . It will work to Quintillion. CREATE OR REPLACE FUNCTION spellNumericValue( pValue numeric) RETURNS text AS $BODY$ DECLARE _dollar bigint = trunc(pValue)::text; _cents int = ((pValue - trunc(pValue))*100)::int; _spelledAmount text = '' ; _brokenOut int[] ; _pos integer = 0; _word text ; BEGIN --lets breakout the number into hundreds into a array WHILE _dollar 0 loop _brokenOut = array_append(_brokenOut, (_dollar%1000)::int); _dollar = trunc(_dollar/1000); _pos = _pos + 1; End Loop; --this works on numbers between 1 to 999 transforming into english words. then goes to the --next set of numbers in the array working backwards as the array was loaded backwards --Meaning the highest value is in the last element of the array _brokenOut --This also assumes words thousands millions, billions... occurs every 10^3 . while _pos 0 loop if _brokenOut[_pos] 99 then SELECT CASE WHEN _brokenOut[_pos] 899 THEN 'Nine Hundred ' WHEN _brokenOut[_pos] 799 THEN 'Eight Hundred ' WHEN _brokenOut[_pos] 699 THEN 'Seven Hundred ' WHEN _brokenOut[_pos] 599 THEN 'Six Hundred ' WHEN _brokenOut[_pos] 499 THEN 'Five Hundred ' WHEN _brokenOut[_pos] 399 THEN 'Four Hundred ' WHEN _brokenOut[_pos] 299 THEN 'Three Hundred ' WHEN _brokenOut[_pos] 199 THEN 'Two Hundred ' WHEN _brokenOut[_pos] 99 THEN 'One Hundred ' else '' end into _word; _spelledAmount = _spelledAmount || _word ; end if; Select Case WHEN _brokenOut[_pos]%100 = 10 THEN 'Ten ' WHEN _brokenOut[_pos]%100 = 11 THEN 'Eleve ' WHEN _brokenOut[_pos]%100 = 12 THEN 'Twelve ' WHEN _brokenOut[_pos]%100 = 13 THEN 'Thirteen ' WHEN _brokenOut[_pos]%100 = 14 THEN 'Fourteen ' WHEN _brokenOut[_pos]%100 = 15 THEN 'Fifteen ' WHEN _brokenOut[_pos]%100 = 16 THEN 'Sixteen ' WHEN _brokenOut[_pos]%100 = 17 THEN 'Seventeen ' WHEN _brokenOut[_pos]%100 = 18 THEN 'Eighteen' WHEN _brokenOut[_pos]%100 = 19 THEN 'Nineteen ' WHEN _brokenOut[_pos]/10%10=2 THEN 'Twenty ' WHEN _brokenOut[_pos]/10%10=3 THEN 'Thirty ' WHEN _brokenOut[_pos]/10%10=4 THEN 'Fourty ' WHEN _brokenOut[_pos]/10%10=5 THEN 'Fifty ' WHEN _brokenOut[_pos]/10%10=6 THEN 'Sixty ' WHEN _brokenOut[_pos]/10%10=7 THEN 'Seventy ' WHEN _brokenOut[_pos]/10%10=8 THEN 'Eighty ' WHEN _brokenOut[_pos]/10%10=9 THEN 'Ninety ' ELSE '' End into _word; _spelledAmount = _spelledAmount || _word; if _brokenOut[_pos]%100 10 or _brokenOut[_pos]%100 20 then SELECT CASE WHEN _brokenOut[_pos]%10 = 1 THEN 'One ' WHEN _brokenOut[_pos]%10 = 2 THEN 'Two' WHEN _brokenOut[_pos]%10 = 3 THEN 'Three ' WHEN _brokenOut[_pos]%10 = 4 THEN 'Four ' WHEN _brokenOut[_pos]%10 = 5 THEN 'Five ' WHEN _brokenOut[_pos]%10 = 6 THEN 'Six ' WHEN _brokenOut[_pos]%10 = 7 THEN 'Seven ' WHEN _brokenOut[_pos]%10 = 8 THEN 'Eight ' WHEN _brokenOut[_pos]%10 = 9 THEN 'Nine ' ELSE '' end into _word; _spelledAmount = _spelledAmount || _word; end if ; If _pos = 2 then _spelledAmount = _spelledAmount || 'Thousand '; elsif _pos = 3 then _spelledAmount = _spelledAmount || 'Million'; elsif _pos = 4 then _spelledAmount = _spelledAmount || 'Billion '; elsif _pos = 5 then _spelledAmount = _spelledAmount || 'Trillion '; elsif _pos = 6 then _spelledAmount = _spelledAmount || 'Quadrillion '; elsif _pos = 7 then _spelledAmount = _spelledAmount || 'Quintillion '; else _spelledAmount = _spelledAmount || ''; end if; _pos = _pos-1; end loop; if _cents = 0 then _spelledAmount = _spelledAmount || ' and Zero cents'; else _spelledAmount = _spelledAmount || 'and ' || _cents::text || '/100 cents'; end if ; return _SpelledAmount; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE
Re: [GENERAL] strange behavior of plpgsql function
I think i may be the way the function is being called?? if you are doing Select fnvs.docrelatedassociatedetails() it will not return any records, it needs to be Select * From fnvs.docrelatedassociatedetails() c k wrote: Hi all, I am facing a small but strange problem when using a plpgsql function as below. CREATE OR REPLACE FUNCTION fnvs.docrelatedassociatedetails(p_docid integer, p_addtype smallint, p_associateid integer, OUT docid integer, OUT associateid integer, OUT addressline1 varchar,OUT addressline2 varchar,OUT addressline3 varchar,OUT city varchar,OUT state varchar,OUT country varchar,OUT postalcode varchar, OUT addtype smallint) RETURNS SETOF record AS $BODY$ begin return query SELECT docid, associateid, addressline1, addressline2, addressline3, city, state, country, postalcode, addtype from docrelatedassociates where docid=p_docid and addtype=p_addtype; return; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER COST 1 ROWS 10; Above function was first written to return a set of records as same as a table, but it didn't worked. Then I written above code and it works but does written any data. When the same function is written using SQL as language it works well. even from above function, it I call the newly written sql langauge function it returns data correctly. What will be the problem. CREATE OR REPLACE FUNCTION software.docrelatedassociatedetails(p_docid integer, p_addtype smallint, p_associateid integer, OUT docid integer, OUT associateid integer, OUT addressline1 varchar,OUT addressline2 varchar,OUT addressline3 varchar,OUT city varchar,OUT state varchar,OUT country varchar,OUT postalcode varchar, OUT addtype smallint) RETURNS SETOF record AS $BODY$ SELECT docid, associateid, addressline1, addressline2, addressline3, city, state, country, postalcode, addtype from docrelatedassociates where docid=$1 and addtype=$2::smallint; $BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER COST 1 ROWS 10; Above function was created few days ago and then I changed the structure of the table which is used to return setof records. After that this problem is occuring. Thanks, CPK
[GENERAL] Retain PREPARE or connect trigger
I am using rsyslog (a syslog to database application) to connect to my postgresql database. It then executes Insert after Insert 100s a minute I have been reading about the PREPARE statement and think that could dramatically increase my insert speed. The problem is, as you all know, that PREPARE only works for the session that it was executed. I cannot make rsyslog prepare the statement when it start's it's session, so how can I use the ability of PREPARE in this situation? I was thinking maybe that there would be away to trigger the prepare statement when the rsyslog user connects. Or is there another option like PREPARE that persists? Thanks for your help. Justin Funk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Retain PREPARE or CONNECT TRIGGER
I am using rsyslog (a syslog to database application) to connect to my postgresql database. It then executes Insert after Insert 100s a minute I have been reading about the PREPARE statement and think that could dramatically increase my insert speed. The problem is, as you all know, that PREPARE only works for the session that it was executed. I cannot make rsyslog prepare the statement when it start's it's session, so how can I use the ability of PREPARE in this situation? I was thinking maybe that there would be away to trigger the prepare statement when the rsyslog user connects. Or is there another option like PREPARE that persists? Thanks for your help. Justin Funk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
Tom Lane wrote:
Robert Haas robertmh...@gmail.com writes:
On Tue, Mar 31, 2009 at 10:44 AM, Greg Stark st...@enterprisedb.com wrote:
On Tue, Mar 31, 2009 at 3:42 PM, Sam Mason s...@samason.me.uk wrote:
string_to_array('',',')::INT[] = invalid input syntax for integer: ""
Oof. That's a good point.
+1. I find this argument much more compelling than anything else
that's been offered up so far.
Yeah. It seems to me that if you consider only the case where the array
elements are text, there's a weak preference for considering '' to be a
single empty string; but as soon as you think about any other datatype,
there's a strong preference to consider it a zero-element list. So I
too have come around to favor the latter interpretation. Do we have
any remaining holdouts?
regards, tom lane
I'm still a hold out, We are taking a string putting it into a array
based on a delimiter. That is very simple and straight forward. Yet
many argue if we want to cast this into another data type the function
should deal with in limited cases.
string_to_array('',',')::INT[] works as proposed
But
string_to_array(',,,', ',' )::INT[] Fails
or
string_to_array('1,2,,4', ',' )::INT[] Fails .
I'm trying to understand the difference between a empty string to a
string with many blank entries between the delimiter.
Consider
',,' = '' once the delimiter is removed . Yet Seven zero length
entries were passed. How is that going to
be handled
In one case it works and yet other cases it fails. This is
inconsistent behavior. Unless all zero length strings are removed or
are treated as NULLs I can't see how casting to another type is going
to work.
If zero length strings are treated as NULLs this creates idea that zero
length strings are = to NULLs.
The input is a string and the output is text[], casting to another
data type is error prone and should be handled by the programmer.
Re: [HACKERS] [GENERAL] string_to_array with empty input
If someone can show me a real world example this logic simplifies the code and has more uses I'll bite I just presently can't see how this works better. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
Martin Gainty wrote:
Split strings into array elements using provided
delimiter
string_to_array('xx~^~yy~^~zz', '~^~')
output: {xx,yy,zz}
http://www.postgresql.org/docs/8.3/interactive/functions-array.html
Sorry thats not the question i'm asking.
We are debating if it makes sense to change the output in certain
cases.
I'm for not returning nulls or returning zero element array.
I'm asking how is the other better by giving a real world example??? I
don't see the plus side at the moment.
Re: [GENERAL] string_to_array with empty input
Sam Mason wrote:
I'd expect 3,2,1 and 1.
That's also a disingenuous example; what would you expect back from:
select count_elements(string_to_array('butter,,milk',','))
I think the semantics you want is what you'd get from:
array_filter_blanks(string_to_array($1,$2))
where I defined array_filter_blanks in my previous post.
I agree the function should not be changing values passed.
Stripping/Dropping empty strings is changing what was passed into the
function instead breaking it into a array.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
Sam Mason wrote:
string_to_array('',',')::INT[] = invalid input syntax for integer:
Which you don't get at the moment; although you do currently get it in
other common cases such as:
string_to_array('1,',',')::INT[]
If you want backwards compatible behaviour you could always bung a
NULLIF in there:
string_to_array(NULLIF('',''),',')::INT[] = NULL
But consider this fails also
select string_to_array('1, , 3', ',' )::int[] = ERROR: invalid input
syntax for integer:
yet this works
select string_to_array('1, 2, 3',',')::int[]
--
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] string_to_array with empty input
Greg Stark wrote:
On Tue, Mar 31, 2009 at 5:48 PM, justin jus...@emproshunts.com wrote:
But consider this fails also
select string_to_array('1, , 3', ',' )::int[] = ERROR: invalid input
syntax for integer: " "
yet this works
select string_to_array('1, 2, 3',',')::int[]
Sure, and the analogous pair string_to_array(' ',',')::int[] and
string_to_array('1 ',',')::int[] behave similarly.
The point is that '' could represent no items or one empty string. We
get to pick which one and in any use case where the string was a list
of objects it's almost certainly intended to be an empty list. And
databases are almost always processing lists of things.
I think the only use case where you want it to be a singleton list of
an empty string is when you're doing string parsing such as building a
lexer or something like that, which is isn't a typical use for sql
code.
I disagree. Casting a string to something else can be a very error
prone to begin with.
Having string_to_array() to deal with that possibility is out of its
scope IMHO.
Consider this. I have intelligent part numbers that need to be split
apart to simplify searching and do math with.
string_to_array(' F-2500-50 ', '-' ) ::int[]
Still fails with an error as expected. what is the difference between
' ' and 'F'
So before doing any thing a test needs to be done to verify the
contents, so it can be casted to something else.
Re: [GENERAL] string_to_array with empty input
This thread being cross posted has made it a bit confusing Greg Stark wrote: Nobody has ever suggested filtering out empty elements or dealing specially with spaces or anything else like that. If you're talking about that then you've missed the original question. "Does anyone want to argue for keeping it the same? Or perhaps argue that a zero-element array is a more sensible result than a one-element array with one empty string? (It doesn't seem like it to me, but maybe somebody thinks so.)" That means to me dropping empty strings or removing values that don't make sense. Then the argument begins what values make since to drop. Just zero length strings or include strings with million white spaces??? One last try. If there's a column called "shopping list" which is a comma-separated list of items to buy in the store and I store '' in it. How many items do you think that means you should go shopping for? Do you think that's one item that should be rejected because it's an empty string? Or do you think that's an empty list with zero items listed? It can't be rejected, Its an Empty shopping list although a worthless shopping list . What would it look like if it was a zero-length list? You can ask what would it look like if it was a shopping list of one item called ''. But I agree both are theoretically consistent, but one is actually useful in 99% of use cases. The other is only useful in unusual cases. I'm still confused which one you want here
Re: [GENERAL] PL/PGSQL arithmetic errors
Just because a result is unexpected does not mean its an incorrect result. No postgresql follows the order of operations as expected. Now looking at the 2 For loops the First does not have a where clause and the Second has a Where not null this could be the cause of the problem. Another note you don't need to do this in nested For loops it can be done in a single select statement using nested queries or by using a join clause Example of a Left Join Select period, id, (col2-avgResults.col2)/AvgResults.dev_col2, (col1 - AvgResults.col1) / AvgResults.dev_col1 FROM scheme.table, Left Join (SELECT period, AVG(col1) AS avg_col1, STDDEV(col1) AS dev_col1, AVG(col2) AS avg_col2, STDDEV(col2) AS dev_col2 FROM scheme.table GROUP BY period Where col1 is not null ) AvgResults On AvgResults.period = scheme.table.period WHERE col1 IS NOT NULL Assuming i don't have any typos this should give you the results you are looking for and be faster. You can throw in a Case statement in the select testing for grav to limit the result down and speed things up to a single column and do the update that why. jc_mich wrote: Hi all! I'm developing an algorithm with PL/PGSQL using statistical operations from a table. I have several differences between expected results and the results generated by the function. I want to know if there are differences in arithmetic operation sintax or if there's any special arithmetical operators precedence between pl/pgsql and other languages. My code looks like this: FOR iterator1 IN SELECT period, AVG(col1) AS avg_col1, STDDEV(col1) AS dev_col1, AVG(col2) AS avg_col2, STDDEV(col2) AS dev_col2 FROM scheme.table GROUP BY period ORDER BY period LOOP FOR iterator2 IN SELECT period, id, col2, col1 FROM scheme.table WHERE col1 IS NOT NULL LOOP IF grav = 0 THEN _standata := (iterator2.col1 - iterator1.avg_col1) / iterator1.dev_col1; ELSE _standata := (iterator2.col2 - iterator1.avg_col2) / iterator1.dev_col2; END IF; UPDATE scheme.table SET standata = _standata WHERE id = iterator2.id AND period=iterator2.period; _standata := 0.0; END LOOP; END LOOP; Thanks!
Re: [GENERAL] string_to_array with empty input
Tom Lane wrote: I agree this seems less than consistent though, especially seeing that you *don't* get a null for a zero-length separator, which if anything is a more poorly defined case. I doubt it'd be a good idea to back-patch a change for this, but I could see altering the definition for 8.4. Does anyone want to argue for keeping it the same? Or perhaps argue that a zero-element array is a more sensible result than a one-element array with one empty string? (It doesn't seem like it to me, but maybe somebody thinks so.) regards, tom lane I like the array to contain single zero length string. A string was passed in although empty, its still a string not a NULL. Returning an empty array implies nothing was passed to the function although something was. That seems kinda odd to me also, give back what was sent in broken into an array. I use this and split_part allot in our database to break apart part numbers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Partitioned tabled not using indexes for full text search
I have a table that is partitioned on a daily basis.
Full text searches used to be respectably fast with large tables (40
million + records) but insert speed would slow down. So I went with a
partitioned approach. But now, it doesn't seem like the indexes are
being used.
Any idea why it would not be using the indexes?
Here are appropriate descriptions and Explains:
syslog=# \d systemevents;
Table public.systemevents
Column |Type | Modifiers
+-+---
message| character varying |
facility | integer |
fromhost | character varying(80) |
priority | integer |
devicereportedtime | timestamp without time zone |
receivedat | timestamp without time zone |
infounitid | integer |
syslogtag | character varying(80) |
message_index_col | tsvector|
Rules:
systemevents_insert_032509 AS
ON INSERT TO systemevents
WHERE new.devicereportedtime '2009-03-24 23:59:59'::timestamp
without time zone AND new.devicereportedtime = '2009-03-25
23:59:59'::timestamp without time zone DO INSTEAD INSERT INTO
systemevents_032509 (message, facility, fromhost, priority,
devicereportedtime, receivedat, infounitid, syslogtag,
message_index_col)
VALUES (new.message, new.facility, new.fromhost, new.priority,
new.devicereportedtime, new.receivedat, new.infounitid, new.syslogtag,
new.message_index_col)
systemevents_insert_032609 AS
ON INSERT TO systemevents
WHERE new.devicereportedtime '2009-03-25 23:59:59'::timestamp
without time zone AND new.devicereportedtime = '2009-03-26
23:59:59'::timestamp without time zone DO INSTEAD INSERT INTO
systemevents_032609 (message, facility, fromhost, priority,
devicereportedtime, receivedat, infounitid, syslogtag,
message_index_col)
VALUES (new.message, new.facility, new.fromhost, new.priority,
new.devicereportedtime, new.receivedat, new.infounitid, new.syslogtag,
new.message_index_col)
systemevents_insert_032709 AS
ON INSERT TO systemevents
WHERE new.devicereportedtime '2009-03-26 23:59:59'::timestamp
without time zone AND new.devicereportedtime = '2009-03-27
23:59:59'::timestamp without time zone DO INSTEAD INSERT INTO
systemevents_032709 (message, facility, fromhost, priority,
devicereportedtime, receivedat, infounitid, syslogtag,
message_index_col)
VALUES (new.message, new.facility, new.fromhost, new.priority,
new.devicereportedtime, new.receivedat, new.infounitid, new.syslogtag,
new.message_index_col)
systemevents_insert_032809 AS
ON INSERT TO systemevents
WHERE new.devicereportedtime '2009-03-27 23:59:59'::timestamp
without time zone AND new.devicereportedtime = '2009-03-28
23:59:59'::timestamp without time zone DO INSTEAD INSERT INTO
systemevents_032809 (message, facility, fromhost, priority,
devicereportedtime, receivedat, infounitid, syslogtag,
message_index_col)
VALUES (new.message, new.facility, new.fromhost, new.priority,
new.devicereportedtime, new.receivedat, new.infounitid, new.syslogtag,
new.message_index_col)
syslog=# EXPLAIN ANALYZE SELECT DeviceReportedTime,
REPLACE(REPLACE(Message,'',''),'','') as Message, Facility,
FromHost, Priority, ReceivedAt, InfoUnitID ,SysLogTag FROM
SystemEvents WHERE message_index_col @@ to_tsquery('english', 'MAIL')
ORDER BY DeviceReportedTime DESC LIMIT 25 OFFSET 0;
QUERY PLAN
-
Limit (cost=61548.87..61548.93 rows=25 width=176) (actual
time=31933.287..31933.425 rows=25 loops=1)
- Sort (cost=61548.87..61551.59 rows=1091 width=176) (actual
time=31933.280..31933.327 rows=25 loops=1)
Sort Key: public.systemevents.devicereportedtime
Sort Method: top-N heapsort Memory: 29kB
- Result (cost=0.00..61518.08 rows=1091 width=176) (actual
time=43.351..28941.144 rows=21307 loops=1)
- Append (cost=0.00..61512.62 rows=1091 width=176)
(actual time=43.337..23706.264 rows=21307 loops=1)
- Seq Scan on systemevents (cost=0.00..13.00
rows=1 width=151) (actual time=0.007..0.007 rows=0 loops=1)
Filter: (message_index_col @@ '''mail'''::tsquery)
- Seq Scan on systemevents_032609 systemevents
(cost=0.00..27869.42 rows=494 width=152) (actual time=43.318..6153.645
rows=9309 loops=1)
Filter: (message_index_col @@ '''mail'''::tsquery)
- Seq Scan on systemevents_032509 systemevents
(cost=0.00..19001.65 rows=339 width=153) (actual time=0.611..5861.674
rows=6239 loops=1)
Filter: (message_index_col @@ '''mail'''::tsquery)
- Seq Scan on systemevents_032709 systemevents
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
Re: [GENERAL] Running Postgresl in a virual machine
Richard Sickler wrote: Hello, I am creating a small web app that uses Postgres. The number of hits/day will be small, maybe a 1000 pages/day. I'm planning on running this in a virtual machine, (Windows Server 2008 with the virtual machine hosting Postgres running Windows Server 2003 R2 x64, or maybe XP x64). The database will have it's own mirrored disks, and the server has plenty of horsepower and RAM. Backup will be copying the quiesced virtual machine disks to a NAS. Does anyone have any experience running Postgres in a virtual machine, or recommendations (or could point me to a place where I could read more about Postgres and Virtual machines)? Thanks. Rich S. Here is something that might interest you http://wiki.postgresql.org/wiki/Running__Installing_PostgreSQL_On_Native_Windows -- 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] [ask] Return Query
ataherster wrote: hai all, i'm trying create function like this CREATE OR REPLACE FUNCTION penjualan(idcb integer) RETURNS SETOF penjualan AS but this function is not work with this error : ERROR: structure of query does not match function result type CONTEXT: PL/pgSQL function penjualan line 6 at RETURN QUERY on the time i try with other table and working well thanks for your help This is because Postgresql does know the structure of the data to be returned. So the choice either use OUT command like so http://www.postgresql.org/docs/current/static/plpgsql-declarations.html Create or Replace Function penjualan(idcb, integer, OUT f1 text, OUT f2 integer) Returns SETOF penjualan AS Or create a new data type describing the data structure http://www.postgresql.org/docs/8.3/static/sql-createtype.html Create Type myTable as ( f1 text, f2 integer) Create or Replace Function penjualan(idcb, integer) Returns SETOF myTable AS -- 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] nulls
Both ways will work. Setting the superseded_after to a far off future will work but will have to set to a real date when it actual does become superseded. The same is true for nulls It boils down to how you and the users want to look at the data. To me to get the all the records that has not been superseded yet is simple either way. Select * from some_table where superseded_after IS NULL or Select * from some_table where superseded_after current_date James B. Byrne wrote: I am confronting a design decision involving null values and I cannot seem to discern which way to go. Therefore, I would like some commentary based on real world experience. The basic issue is episodic duration, expressed as columns named dt_effective_from and dt_superseded_after. Both are datetime types containing values normalized to utc. You see where this is going. The issue is what to enter when the value is known to be unknown, as in some indeterminate future date, which may be never. I read that relational set values should never be null, as null is indeterminate for WHERE clauses and may result in unexpected results. On the other hand, setting some artificially excessive future date seems in its place seems, to me, to have its own problems. Since this issue must have been dealt with time and time again in the past I would like to know what, if any, consensus has been reached on the matter. What is the best way to proceed? -- 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] mdf
There are a couple of tools that will open an MDF file buy you have to pay for them http://www.sqldatabaserepair.com/ http://www.mssqldatabaserecovery.com/ Another option is you need to learn how the MSSQL server is setup, and what version. Get your hands on that Version of SQL install it and copy this file into the data directory then try to mount it. If you fail to duplicate the setup of the server that created mdf file it can't be mounted. It will tell you the reason. It would be easier if the people that sent you the MDF did a backup of the MSSQL Server, send you the backup, download a eval of MSSQL restore the backup then migrate. Ary Pezo Silvano wrote: hi, thank you for your comments. my problem is that the mdf file has been sent to me by email in a zip file. i extracted the file and it is 8 GB. i don't know how i should import or open it with postgres. best, ary Frn: John R Pierce [pie...@hogranch.com] Skickat: den 7 mars 2009 01:16 Till: Ary Pezo Silvano Kopia: pgsql-general@postgresql.org mne: Re: [GENERAL] mdf Ary Pezo Silvano wrote: hi, how can i import a mdf database into postgres? do you have access to the SQL Server that this MDF file came from? Install the Postgres client for windows, along with a Postgres ODBC driver on that server, and use the Data Translation Services in SQL Server Enterprise to export the data from SQL Server to Postgres (which can be on a different server, using a postgres network connection, if you setup everything correctly). Otherwise, use SQL Server to export the tables seperately as CSV files, and import them into Postgres.
[GENERAL] Get IP addresses from tsvectors
Greetings, I have a table with a column with type tsvector. It contains the result of to_tsvector() of varchar field in the table. What I'd like to do is be able to search through the table and find all of the distinct IP addresses. Any idea how to turn: SELECT message_index_col FROM systemevents LIMIT 10; message_index_col - 'leas':4 'return':2 'leas':2 'found':1 'address':5 'hardwar':4 '65.110.236.113':6 '00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'free':14 'leas':15 'martin':12 'network':11 'dhcpdiscov':1 '10.10.94.126':10 'leas':2 'found':1 'address':5 'request':4 '65.110.236.113':6 'ip':4 'leas':2,5 'ident':7 'hardwar':1 'leas':2 'choos':1 'address':5 'request':4 'leas':2 'return':1 '65.110.236.113':3 '00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'dhcpdiscov':1 '10.10.94.126':10 '00':5 '17':6 '1d':9 '27':8 '4c':10 'f2':7 'via':11 'dhcpoffer':1 '10.10.94.126':12 '65.110.236.113':3 '451':6 'tri':9 '4.7.1':7 'later':11 'pleas':8 'milter':2 'reject':5 'tempfail':12 'n29c3q08020087':1 'kgan...@iastate.edu':4 into IP_ADDRESSES - 65.110.236.113 10.10.94.126 Thanks for the help... Justin Funk -- 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] foxpro, odbc, data types and unnecessary convertions
Fernando Moreno wrote: Hi all, I'm using visual foxpro 9 -not my decision- for a client application. Statements are writen as the typical sql string and sent through ODBC. i like foxpro it has its quirks as do all languages. Only concern if this is a new app Foxpro has been killed by MS. Version 9 is the last so no 64 bit support and at the mercy of MS to keep 32 bit support working down the road For numbers, I have to convert them first to string and then remove the spaces, the code looks like this: sql_string = "some sql" + alltrim( str( some_number ) ) + " more sql"; I can combine alltrim and Why the alltrim ? white spaces don't hurt. take a look at Text EndText with TextMerge it has a few gotchas here and there but on complex sql string it makes life allot easier. str in a third function but it's still tricky. A shorter and presumably better way to do the same is: sql_string = "some_column = ?foxpro_variable ". don't use ? its from the DOS days, it does some odd conversions because it is a hold over The problem with the last option is that, watching the pgsql log, values are sent this way: '12345'::float(8), so for every numeric value, no matter its type, I'm sending 12 characters more and the server is doing convertions that I don't need. Having a lot of foreign keys and other numeric data, I think this behaviour is not so good for network (remote and poor connection) and server performance. I'm almost decided to keep doing the trim/str thing, but my question is: am I exaggerating? what would you do
Re: [GENERAL] foxpro, odbc, data types and unnecessary convertions
Completely forgot take a look at Cursor Adapter Class, also any cursor in foxpro can be made be updateable with CURSORSETPROP( ) function removing the problem of writing Update's and Inserts Fernando Moreno wrote: Hi all, I'm using visual foxpro 9 -not my decision- for a client application. Statements are writen as the typical sql string and sent through ODBC. For numbers, I have to convert them first to string and then remove the spaces, the code looks like this: sql_string = some sql + alltrim( str( some_number ) ) + more sql; I can combine alltrim and str in a third function but it's still tricky. A shorter and presumably better way to do the same is: sql_string = some_column = ?foxpro_variable . The problem with the last option is that, watching the pgsql log, values are sent this way: '12345'::float(8), so for every numeric value, no matter its type, I'm sending 12 characters more and the server is doing convertions that I don't need. Having a lot of foreign keys and other numeric data, I think this behaviour is not so good for network (remote and poor connection) and server performance. I'm almost decided to keep doing the trim/str thing, but my question is: am I exaggerating? what would you do? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres SRPMs for RHEL
Is there a reason why the source RPMs for PG 8.1.16 on RHEL don't show up here? http://www.postgresql.org/ftp/binary/v8.1.16/linux/srpms/redhat/rhel-4-i386/ If I cycle through the versions, the last version in the 8.1 branch I can find with source RPMs is 8.1.14. -- Justin Pasher -- 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] Postgres SRPMs for RHEL
Joshua D. Drake wrote: On Wed, 2009-02-25 at 12:10 -0600, Justin Pasher wrote: Is there a reason why the source RPMs for PG 8.1.16 on RHEL don't show up here? http://www.postgresql.org/ftp/binary/v8.1.16/linux/srpms/redhat/rhel-4-i386/ If I cycle through the versions, the last version in the 8.1 branch I can find with source RPMs is 8.1.14. http://yum.pgsqlrpms.org/8.1/redhat/rhel-4ES-i386/ Unless I'm just looking for the wrong filename, I still can't fine the source RPMs on the yum repo either, just the regular RPMs. -- Justin Pasher -- 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] Postgres SRPMs for RHEL
Devrim GÜNDÜZ wrote: On Wed, 2009-02-25 at 12:10 -0600, Justin Pasher wrote: Is there a reason why the source RPMs for PG 8.1.16 on RHEL don't show up here? http://www.postgresql.org/ftp/binary/v8.1.16/linux/srpms/redhat/rhel-4-i386/ 'cause I was a bit lazy to sync srpms from main repository. It is my bad, and will start pushing packages later today. If I cycle through the versions, the last version in the 8.1 branch I can find with source RPMs is 8.1.14. Please take a look at here: http://yum.pgsqlrpms.org/srpms/8.1/redhat/rhel-4-i386/repoview/postgresql.html Using pgsqlrpms site, you can download SRPMs using yum: yumdownloader --enablerepo pgdg81-source --source postgresql -HTH. That'll work! Thanks. -- Justin Pasher -- 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] Remote Connection
Bob Pawley wrote: Hi I've been operating on localhost successfully for some time. I am now attempting to access a Postgresql 8.3 database installed on one of my other computers conneted through a router. Is this possible? My first attempts have been with Postgresql Admin. I changed host to the IP address of the computer and input the other info. I also ran - listen virtual; on the receiving server. It hasn't connected successfully. I get the message 'Server not listening' What else can I do?? Bob Postgresql is configure by default to drop all connections that are not localhost see http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html to make sure its configured to accept connections other than localhost Second on the router make sure port 5432 is open and porting to the Postgresql which is the default port.
[GENERAL] Continual increase of age(datfrozenxid) for template0
Hello, I'm trying to understand if this is normal. This is a Postgres 8.1.15 server (the same one in use when I reported the autovacuum problem here: http://archives.postgresql.org/pgsql-general/2009-01/msg00404.php). Since most of our servers are still stuck on Postgres 7.4, I don't have nearly the same experience in regards to the inner workings of PG8, so I'm trying to understand if this is normal. I currently have autovacuum disabled due to the segfault problem under 8.1.15 (waiting for Debian to release a new 8.1.16 stable package). What I've noticed is that the age(datfrozenxid) of the template0 database grows quite a bit over a short period of time (under two weeks). Here is a snippet from the current server: postgres=# SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2; datname |age ---+ database1 | 1389525200 database2 | 1389726011 database3 | 1389780361 ... template0 | 1722583868 (32 rows) As you can see, it is starting to approach the 2 billion mark. If I change the datallowconn setting to TRUE, connect to template0, then perform a VACUUM FREEZE, the age(datfrozenxid) goes way down (probably around the 3 million mark). However, over time (typically less than two weeks), the number slowly creeps higher and higher, until our custom monitoring software starts spitting out a warning/error that the database should be vacuumed soon. If I look at a PG7 database where I have done the same thing in the past few weeks, the number is still really low (around 5 million). Because I have autovacuum disabled for the time being, I have a simple smart_vacuumdb script that runs every day to keep the databases vacuumed. It simple selects the top X% of the databases (based upon age(datfrozenxid)), and calls the Postgres vacuumdb script if the age() is greater than a certain threshold (around 1.5 billion). This helps keep the normal database away from xid wraparound, but since template0 does not allow connections, it never gets vacuumed. What I'm trying to figure out is whether this is normal, expected behavior. It doesn't seem to happen on the PG7.4 server (or at least not nearly to this degree), but the work load on that machine is much different than this PG8.1 server. The PG8.1 server runs a lot more queries across all databases (and probably quite a few within transactions). The smart_vacuumdb script helps keep the important databases in check, but I'm worried about template0. During the time I started this email and now, the age(datfrozenxid) of the database has gone up by 4 million. Thanks. -- Justin Pasher -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I turn on query logger?
Jennifer Trey wrote: I am new to postgreSQL and I am running queries from JAVA and would like to see what queries are being run. How can I achieve that ? I am running PostgreSQL 8.3 on Windows and like using pgAdmin to do most things. Open PG admin from the menu bar Tools-Server Status- Status Tab will have current running queries and active users -- 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 plpgsql functions
Chris wrote: Hi all, I have a few plpgsql functions to debug to see why they are slow. They consist of a bunch of sql statements using new.* / old.* variables (ie not using EXECUTE, the sql is being called directly). Is there a way to capture the actual sql that's being executed with variables substituted in, or even an easy way to log the variables being used? I couldn't see anything in the manual but maybe I missed something - any pointers appreciated. I tried http://pgfoundry.org/projects/edb-debugger/ but after loading the module, the functions wouldn't run properly (can't remember the error message but I can do it again if need be). Debugging sql functions is sometimes a not to fun event. pgdebugger has some gotchas that will bite you big time. One big item don't run pgdebugger in a production machine i've had lock process and its done some other odd things. I normally run the pgdebugger on a windows install as it will install it for you. and use pgadmin two step through the code. One option use Raise Notice to see whats in a variable example RAISE NOTICE 'Var1 %, Var2 %, ' , MemoryVar1, MemoryVar2 ; then look whats returned to the client. Again i use pgAdmin for this as it shows me all the messages sent from the server and keeps a nice easy to read history. -- 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] calculating elapsed times between timestamps
rhubbell wrote: (posted on novice too, no idea what difference is between lists) I have been trying to do this and have been unsuccessful so far. I have a table: perf: timestamp = timestamp with time zone timeelapsed = numeric bobble = text timeelapsed records are the time elapsed metric in seconds. e.g. 0.350058078765869 Typical scenario is that I'll have multiple entries where timeelapsed is greater than some value and will be greater than that value for some time interval. I want to find the length of those intervals. select timestamp, timeelapsed, bobble from perf where bobble like pokerflat and timeelapsed 0.4; The records returned by that query will have an oldest and newest timestamp for which I would like to calculate the interval. I'm not real clear on what you are asking here with oldest and newest timestamps and getting the interval, please clarify I found lots of examples of doing arithmetic on timestamps but I never saw any extracting data from a table. All the examples I found were using now() or current_date + 3 or the like. Just substitute current_date or now() appears in the examples with the column name Do aggregate function work on time data? For date time function go here http://www.postgresql.org/docs/8.3/static/functions-datetime.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] compiling libpq.lb into qsqlpsql.dll
Hello all. I have a problem and tech support at troll tech has been unable to help me resolve. I am trying to compile libpq.lib from postgresql 8.3.5 into QT's version 4.4.3 qsqlpsql.dll. using Visual Studio 2008 Built libpq.lib and libpq.dll with no problesm using nmake. Now comes time to build the qsqlpsql.dll following the way out of date instructions. Got all paths for includes and libs taken care of. Now compiling from nmake get billionz link errors LNK2019: unresolved external symbol I then imported the pro and make files in Visual Studio project to better figure out why it can't find external symbols Right off the bat Visual Studio reduces the number of external symbols not found to 5 1libpq.lib(fe-connect.obj) : error LNK2019: unresolved external symbol __imp__deletesecuritycont...@4 referenced in function _closePGconn 1libpq.lib(fe-connect.obj) : error LNK2019: unresolved external symbol __imp__freecredentialshan...@4 referenced in function _closePGconn 1libpq.lib(fe-auth.obj) : error LNK2019: unresolved external symbol _freecontextbuf...@4 referenced in function _pg_SSPI_continue 1libpq.lib(fe-auth.obj) : error LNK2019: unresolved external symbol _initializesecurityconte...@48 referenced in function _pg_SSPI_continue 1libpq.lib(fe-auth.obj) : error LNK2019: unresolved external symbol _acquirecredentialshand...@36 referenced in function _pg_SSPI_startup Now i'm stuck and can't figure out the next step. my guess is i don't have all the paths for include and libs set correctly. The idea here is get SSL built in qsqlpsql.dll which by default its not enabled. But i wanted to start off just to get plain jane libpq to compile with qsqlpsql.dll before i make sure libpq has SSL turned on. thanks
Re: [GENERAL] performance advice needed: join vs explicit subselect
Karsten Hilbert wrote: Hello all, maybe some general advice can be had on this: table test_results modified_by integer foreign key staff(pk), intended_reviewer integer foreign key staff(pk), actual_reviewer integer foreign key staff(pk) (this table will contain millions of rows) table staff pk integer name text (this table will contain at most 50 rows) Now I want to set up a view which aggregates test results with staff names for all three foreign keys. This would mean I would either have to - join test_results to staff three times, once for each of the foreign keys, this is going to be messy with tracking table aliases, duplicate column names etc - write three explicit sub-selects for the columns I want to denormalize into the view definition Select testresults.*, Modifer.Name, Intended.name, Actual.name from testresults left join (Select pk, name from staff) Modifer on Modifer.pk = testresults.modified_by left join (Select pk, name from staff) Intended on Reviewer.pk = testresults.intended_reviewer left join (Select pk, name from staff) Actual on pk = testresults.actual_reviewer This is what i think you are after. You can do this via nested queries also for each name -- 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] performance advice needed: join vs explicit subselect
typo sorry justin wrote: Karsten Hilbert wrote: Hello all, maybe some general advice can be had on this: table test_results modified_by integer foreign key staff(pk), intended_reviewer integer foreign key staff(pk), actual_reviewer integer foreign key staff(pk) (this table will contain millions of rows) table staff pk integer name text (this table will contain at most 50 rows) Now I want to set up a view which aggregates test results with staff names for all three foreign keys. This would mean I would either have to - join test_results to staff three times, once for each of the foreign keys, this is going to be messy with tracking table aliases, duplicate column names etc - write three explicit sub-selects for the columns I want to denormalize into the view definition Select testresults.*, Modifer.Name, Intended.name, Actual.name from testresults left join (Select pk, name from staff) Modifer on Modifer.pk = testresults.modified_by left join (Select pk, name from staff) Intended on Inteded.pk = testresults.intended_reviewer left join (Select pk, name from staff) Actual on Actual.pk = testresults.actual_reviewer This is what i think you are after. You can do this via nested queries also for each name -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rollback of Query Cancellation
Abdul Rahman wrote: Dear All, Yesterday I canceled a running query because it was taking long time (more than 12 minutes) to delete lots of records. Today when I executed the same query it hardly took few seconds to finish. It clearly explores that PostgreSQL does not perform rollback action. Is it true? Regards, Abdul Rehman. The transaction would have been rolled back. this makes me think the delete was wrapped in individual transactions for a set of records or per record. With out more information an intelligent answer is kind of hard to give. What does the sql statement look like? Where was the statement run? How was the statement killed? What do the logs show?
Re: [GENERAL] Autovacuum daemon terminated by signal 11
-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Saturday, January 17, 2009 9:50 AM To: Alvaro Herrera Cc: Justin Pasher; pgsql-general@postgresql.org Subject: Re: [GENERAL] Autovacuum daemon terminated by signal 11 Alvaro Herrera alvhe...@commandprompt.com writes: Hmm, in retrospect this is pretty obviously buggy. I can't say that it's that easy for me to reproduce it though; I definitely can't make it crash. Maybe by sheer luck, the new TopTransactionContext pointer points to the same memory area that the old was stored in. Yeah, there could be some platform dependency involved. I'm guessing different structs that happen to fall into the same palloc size category on one platform but not another. Anyway, it happens consistently on my HP box. I find that your proposed patch fixes it, but makes the normal path crash :-( --- the loop in do_autovacuum has to be executed in AutovacMemCxt, because it creates an Oid List that gets passed to vacuum() and had better not be in a transaction-lifetime context. The attached modified patch works for me. regards, tom lane I tried both Alvaro's patch and your patch, and I actually got the same results from both. I didn't experience a crash when autovacuum kicked in or when manually performing a vacuum on the database (if that's what you meant by the normal path). At any rate, everything seems to be working properly for me with the patch. Thanks! Justin Pasher -- 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] Autovacuum daemon terminated by signal 11
Tom Lane wrote: I read it like this: #0 0x0827441d in MemoryContextAlloc () -- real #1 0x08274467 in MemoryContextStrdup ()-- real #2 0x0826501c in database_getflatfilename () -- real #3 0x0826504e in database_getflatfilename () -- must be write_database_file #4 0x08265ec1 in AtEOXact_UpdateFlatFiles () -- real #5 0x080a9111 in RecordTransactionCommit ()-- must be CommitTransaction #6 0x080a93a7 in CommitTransactionCommand () -- real #7 0x081a6c3b in autovac_stopped ()-- must be process_whole_db #8 0x081a75cd in autovac_start () -- real #9 0x081ae33c in ClosePostmasterPorts () -- must be ServerLoop #10 0x081af058 in PostmasterMain () #11 0x0816b3e2 in main () although this requires one or two leaps of faith about single-call static functions getting inlined so that they don't produce a callstack entry (in particular that must have happened to AutoVacMain). In any case, it's very hard to see how MemoryContextAlloc would dump core unless the method pointer of the context it was pointed to was clobbered. So I'm pretty sure that's what happened, and now we must work backwards to how it happened, Justin, it's entirely possible that the only way we'll figure it out is for a developer to go poking at the entrails. Are you in a position to give Alvaro or me ssh access to your test machine? regards, tom lane OK. Here's an update on this. I was able to reduce the database cluster down to just one real database (aside from template0/1 and postgres) and I was still getting the segfault. I was even able to delete all the data from a lot of the sensitive tables and still get the segfault. At least this means it's easier for me to give access to the DB now if need be. I recompiled from the Debian source package and added --enable-cassert (--enable-debug was already there). I replaced the Debian standard packages with the recompiled versions and started up the cluster. Now it is hitting a failure on one of the assert lines, and the log message is a little different. 2009-01-16 15:24:48 CST LOG: transaction ID wrap limit is 1076038431, limited by database template1 TRAP: BadArgument(!(((context) != ((void *)0) (Node*)((context)))-type) == T_AllocSetContext, File: mcxt.c, Line: 502) 2009-01-16 15:24:52 CST LOG: autovacuum process (PID 7066) was terminated by signal 6 2009-01-16 15:24:52 CST LOG: terminating any other active server processes A new backtrace from the core dump is below, although it looks almost identical to me. -- hostname:/var/lib/postgresql/8.1# gdb /usr/lib/postgresql/8.1/bin/postmaster mc-db2/core GNU gdb 6.4.90-debian Copyright (C) 2006 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as i486-linux-gnu...(no debugging symbols found) Using host libthread_db library /lib/tls/i686/cmov/libthread_db.so.1. warning: Can't read pathname for load map: Input/output error. Reading symbols from /lib/libpam.so.0...(no debugging symbols found)...done. Loaded symbols for /lib/libpam.so.0 Reading symbols from /usr/lib/i686/cmov/libssl.so.0.9.8...(no debugging symbols found)...done. Loaded symbols for /usr/lib/i686/cmov/libssl.so.0.9.8 Reading symbols from /usr/lib/i686/cmov/libcrypto.so.0.9.8...(no debugging symbols found)...done. Loaded symbols for /usr/lib/i686/cmov/libcrypto.so.0.9.8 Reading symbols from /usr/lib/libkrb5.so.3...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libkrb5.so.3 Reading symbols from /lib/libcom_err.so.2...(no debugging symbols found)...done. Loaded symbols for /lib/libcom_err.so.2 Reading symbols from /lib/tls/i686/cmov/libcrypt.so.1... (no debugging symbols found)...done. Loaded symbols for /lib/tls/i686/cmov/libcrypt.so.1 Reading symbols from /lib/tls/i686/cmov/libdl.so.2...(no debugging symbols found)...done. Loaded symbols for /lib/tls/i686/cmov/libdl.so.2 Reading symbols from /lib/tls/i686/cmov/libm.so.6...(no debugging symbols found)...done. Loaded symbols for /lib/tls/i686/cmov/libm.so.6 Reading symbols from /lib/tls/i686/cmov/libc.so.6...(no debugging symbols found)...done. Loaded symbols for /lib/tls/i686/cmov/libc.so.6 Reading symbols from /usr/lib/libz.so.1...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libz.so.1 Reading symbols from /usr/lib/libk5crypto.so.3... (no debugging symbols found)...done. Loaded symbols for /usr/lib/libk5crypto.so.3 Reading symbols from /lib/tls/i686/cmov/libresolv.so.2...(no debugging symbols found)...done. Loaded symbols for /lib/tls/i686/cmov/libresolv.so.2 Reading symbols from /lib/ld-linux.so.2...(no debugging symbols found)...done. Loaded symbols
Re: [GENERAL] Autovacuum daemon terminated by signal 11
Tom Lane wrote:
Justin Pasher just...@newmediagateway.com writes:
I recompiled from the Debian source package and added --enable-cassert
(--enable-debug was already there). I replaced the Debian standard
packages with the recompiled versions and started up the cluster. Now it
is hitting a failure on one of the assert lines, and the log message is
a little different.
TRAP: BadArgument(!(((context) != ((void *)0)
(Node*)((context)))-type) == T_AllocSetContext, File:
mcxt.c, Line: 502)
Well, that confirms the thought that the memory context is clobbered,
but we're not any closer to understanding why.
#1 0xb7c37811 in raise () from /lib/tls/i686/cmov/libc.so.6
#2 0xb7c38fb9 in abort () from /lib/tls/i686/cmov/libc.so.6
#3 0x0828cdf3 in ExceptionalCondition ()
#4 0x082a8cd2 in MemoryContextAlloc ()
#5 0x082a8d67 in MemoryContextStrdup ()
#6 0x0829749c in database_getflatfilename ()
#7 0x082974ce in database_getflatfilename ()
#8 0x08298341 in AtEOXact_UpdateFlatFiles ()
#9 0x080bcc81 in RecordTransactionCommit ()
#10 0x080bcf8f in CommitTransactionCommand ()
#11 0x081cd1eb in autovac_stopped ()
#12 0x081cdbcd in autovac_start ()
#13 0x081d4c0c in ClosePostmasterPorts ()
#14 0x081d5968 in PostmasterMain ()
#15 0x0818bd22 in main ()
... and you've seemingly not managed to install the debug symbols where
gdb can find them.
regards, tom lane
Dang it. I wonder why the --enable-debug option doesn't seem to actually
be enabling debug. :( For reference, here is the configure command that
the package uses according to the config.log (in case you spot anything
wrong).
/usr/src/postgres-8.1.15/postgresql-8.1-8.1.15/build-tree/postgresql-8.1.15/configure
--build=i486-linux-gnu --prefix=/usr --includedir=${prefix}/include
--mandir=${prefix}/share/man --infodir=${prefix}/share/info
--sysconfdir=/etc --localstatedir=/var
--libexecdir=${prefix}/lib/postgresql-8.1 --disable-maintainer-mode
--disable-dependency-tracking --srcdir=.
--mandir=${prefix}/share/postgresql/8.1/man
--with-docdir=${prefix}/share/doc/postgresql-doc-8.1
--datadir=${prefix}/share/postgresql/8.1
--bindir=${prefix}/lib/postgresql/8.1/bin
--includedir=${prefix}/include/postgresql/ --enable-nls
--enable-integer-datetimes --enable-thread-safety --enable-debug
--enable-cassert --disable-rpath --with-tcl --with-perl --with-python
--with-pam --with-krb5 --with-openssl --with-gnu-ld
--with-tclconfig=/usr/lib/tcl8.4 --with-tkconfig=/usr/lib/tk8.4
--with-includes=/usr/include/tcl8.4 --with-pgport=5432 CFLAGS=-g -Wall
-O2 -fPIC LDFLAGS=-Wl,--as-needed
I'm going to try a compile from scratch from the 8.1.15 source tarball
and see if I can get better results. I'll let you know (probably next
week). Thanks.
Justin Pasher
--
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] Autovacuum daemon terminated by signal 11
(argc=value optimized out, argv=value optimized out) at autovacuum.c:559 #12 0x081b1879 in autovac_start () at autovacuum.c:174 #13 0x081b7f78 in ServerLoop () at postmaster.c:1269 #14 0x081b8bad in PostmasterMain (argc=3, argv=0x836b508) at postmaster.c:943 #15 0x08175609 in main (argc=3, argv=0x836b508) at main.c:265 Justin Pasher -- 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] Autovacuum daemon terminated by signal 11
Richard Huxton wrote: Justin Pasher wrote: Hello, I have a server running PostgreSQL 8.1.15-0etch1 (Debian etch) that was recently put into production. Last week a developer started having a problem with his psql connection being terminated every couple of minutes when he was running a query. When I look through the logs, I noticed this message. 2009-01-09 08:09:46 CST LOG: autovacuum process (PID 15012) was terminated by signal 11 Segmentation fault - probably a bug or bad RAM. It's a relatively new machine, but that's obviously a possibility with any hardware. I haven't seen any other programs experiencing problems on the box, but the Postgres daemon is the one that is primarily utilized, so it's a little biased toward that. I looked through the logs some more and I noticed that this was occurring every minute or so. The database is a pretty heavily utilized system (judging by the age(datfrozenxid) from pg_database, the system had run approximately 500 million queries in less than a week). I noticed that right before every autovacuum termination, it tried to autovacuum a database. 2009-01-09 08:09:46 CST LOG: transaction ID wrap limit is 4563352, limited by database database_name It was always showing the same database, so I decided to manually vacuum the database. Once that was done (it was successful the first time without errors), the problem seemed to go away. I went ahead and manually vacuumed the remaining databases just to take care of the potential xid wraparound issue. I'd be suspicious of possible corruption in autovacuum's internal data. Can you trace these problems back to a power-outage or system crash? It doesn't look like database_name itself since you vacuumed that successfully. If autovacuum is running normally now, that might indicate it was something in the way autovacuum was keeping track of database_name. The server hasn't been rebooted since it was installed (about 9 months ago, but only being utilized within the past month), so there haven't been any crashes or power outages. The only abnormal things I can find in the Postgres logs are the autovacuum segfaults. Looking in the logs today, it looks like it's still happening (once again on a different database). I manually vacuumed that one database and the problem went away (for now). Are there any internal Postgres tables I can look at that may shed some light on this? Any particular maintenance commands that could be run for repair? It's also probably worth running some memory tests on the server - (memtest86 or similar) to see if that shows anything. Was it *always* the autovacuum process getting sig11? If not then it might just be a pattern of usage that makes it more likely to use some bad RAM I might try the memtest if we can actually get the databases off of the server to allow some downtime. None of the logs indicate anything else acting abnormally or being terminated abnormally, just the autovacuum daemon. From what I can tell, the segfaults only when the databases pass the half way point (when age(datfrozenxid) exceeds around 15). When this is not the case, the segfaults do not occur according to the logs. Justin Pasher -- 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] Autovacuum daemon terminated by signal 11
Tom Lane wrote: Justin Pasher just...@newmediagateway.com writes: Richard Huxton wrote: Segmentation fault - probably a bug or bad RAM. It's a relatively new machine, but that's obviously a possibility with any hardware. I haven't seen any other programs experiencing problems on the box, but the Postgres daemon is the one that is primarily utilized, so it's a little biased toward that. I agree that the behavior seems a bit too specific to be a hardware issue. Can you get a stack trace from the crash? You might need to restart the postmaster under ulimit -c unlimited to get a core dump from the crashing autovacuum process. regards, tom lane I'm working on getting the database running on another server so I can perform more tests. So far I was able to get a copy of the cluster up and running. Once the autovacuum process kicked in, it started experiencing the same segfault on the new box. At this point, the hardware on the original box no longer seems to be a culprit (assuming the data files themselves aren't corrupted and I didn't just bring the corruption along with the cluster). I'll let you know when I get a chance to get a core dump from the process. I assume I will need a version of Postgres built with debug symbols for it to be useful? I'm not seeing one in the standard Debian repositories, so I might have to compile from source. Justin Pasher -- 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] Autovacuum daemon terminated by signal 11
Tom Lane wrote: Having debug symbols would be more useful, but unless the binary is totally stripped, a backtrace might provide enough info without that. Try it and see if you get any function names in the trace, or only numbers. (BTW, does Debian have anything comparable to Red Hat's debuginfo packages? You might be able to get the debug symbols without having to recompile for yourself. Recompile is a bit of a pain since you have to take care to match the original compilation options exactly.) regards, tom lane Alrighty. Here's what I got (assuming I did this right). My untrained eyes see some stuff regarding memory allocation. I wonder if overly aggressive memory related tweaks in the config file are causing the problem? I don't recall making any changes to the config file within a short time period of the problem starting, but let me know if I need to post any config settings. hostname:/var/lib/postgresql/8.1/mc-db2# gdb /usr/lib/postgresql/8.1/bin/postmaster core GNU gdb 6.4.90-debian Copyright (C) 2006 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as i486-linux-gnu...(no debugging symbols found) Using host libthread_db library /lib/tls/i686/cmov/libthread_db.so.1. warning: Can't read pathname for load map: Input/output error. Reading symbols from /lib/libpam.so.0...(no debugging symbols found)...done. Loaded symbols for /lib/libpam.so.0 Reading symbols from /usr/lib/i686/cmov/libssl.so.0.9.8...(no debugging symbols found)...done. Loaded symbols for /usr/lib/i686/cmov/libssl.so.0.9.8 Reading symbols from /usr/lib/i686/cmov/libcrypto.so.0.9.8...(no debugging symbols found)...done. Loaded symbols for /usr/lib/i686/cmov/libcrypto.so.0.9.8 Reading symbols from /usr/lib/libkrb5.so.3...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libkrb5.so.3 Reading symbols from /lib/libcom_err.so.2...(no debugging symbols found)...done. Loaded symbols for /lib/libcom_err.so.2 Reading symbols from /lib/tls/i686/cmov/libcrypt.so.1... (no debugging symbols found)...done. Loaded symbols for /lib/tls/i686/cmov/libcrypt.so.1 Reading symbols from /lib/tls/i686/cmov/libdl.so.2...(no debugging symbols found)...done. Loaded symbols for /lib/tls/i686/cmov/libdl.so.2 Reading symbols from /lib/tls/i686/cmov/libm.so.6...(no debugging symbols found)...done. Loaded symbols for /lib/tls/i686/cmov/libm.so.6 Reading symbols from /lib/tls/i686/cmov/libc.so.6...(no debugging symbols found)...done. Loaded symbols for /lib/tls/i686/cmov/libc.so.6 Reading symbols from /usr/lib/libz.so.1...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libz.so.1 Reading symbols from /usr/lib/libk5crypto.so.3... (no debugging symbols found)...done. Loaded symbols for /usr/lib/libk5crypto.so.3 Reading symbols from /lib/tls/i686/cmov/libresolv.so.2...(no debugging symbols found)...done. Loaded symbols for /lib/tls/i686/cmov/libresolv.so.2 Reading symbols from /lib/ld-linux.so.2...(no debugging symbols found)...done. Loaded symbols for /lib/ld-linux.so.2 Reading symbols from /usr/lib/libkrb5support.so.0...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libkrb5support.so.0 (no debugging symbols found) Core was generated by `postgres: autovacuum process mc_dairyqueen '. Program terminated with signal 11, Segmentation fault. #0 0x0827441d in MemoryContextAlloc () (gdb) bt #0 0x0827441d in MemoryContextAlloc () #1 0x08274467 in MemoryContextStrdup () #2 0x0826501c in database_getflatfilename () #3 0x0826504e in database_getflatfilename () #4 0x08265ec1 in AtEOXact_UpdateFlatFiles () #5 0x080a9111 in RecordTransactionCommit () #6 0x080a93a7 in CommitTransactionCommand () #7 0x081a6c3b in autovac_stopped () #8 0x081a75cd in autovac_start () #9 0x081ae33c in ClosePostmasterPorts () #10 0x081af058 in PostmasterMain () #11 0x0816b3e2 in main () -- 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] Autovacuum daemon terminated by signal 11
Tom Lane wrote: I read it like this: #0 0x0827441d in MemoryContextAlloc () -- real #1 0x08274467 in MemoryContextStrdup ()-- real #2 0x0826501c in database_getflatfilename () -- real #3 0x0826504e in database_getflatfilename () -- must be write_database_file #4 0x08265ec1 in AtEOXact_UpdateFlatFiles () -- real #5 0x080a9111 in RecordTransactionCommit ()-- must be CommitTransaction #6 0x080a93a7 in CommitTransactionCommand () -- real #7 0x081a6c3b in autovac_stopped ()-- must be process_whole_db #8 0x081a75cd in autovac_start () -- real #9 0x081ae33c in ClosePostmasterPorts () -- must be ServerLoop #10 0x081af058 in PostmasterMain () #11 0x0816b3e2 in main () although this requires one or two leaps of faith about single-call static functions getting inlined so that they don't produce a callstack entry (in particular that must have happened to AutoVacMain). In any case, it's very hard to see how MemoryContextAlloc would dump core unless the method pointer of the context it was pointed to was clobbered. So I'm pretty sure that's what happened, and now we must work backwards to how it happened, Justin, it's entirely possible that the only way we'll figure it out is for a developer to go poking at the entrails. Are you in a position to give Alvaro or me ssh access to your test machine? regards, tom lane I'm currently working on recompiling Postgres with the new configure parameters. I'm trying to go the easier route by downloading the Debian source package, add the new options, compile, then install the package. Hopefully this will give the closest possible binary to the current one. Incidentally, the --enable-debug option is already set for the Debian package (I did have to add --enable-cassert though). I'll let you know once I get it up if things work properly. As far as access to the machine, I'll contact you off-list if I can work something out for that. The data is not overly sensitive, but it's still client data nonetheless. I'll try to make a copy of the cluster and try to reduce the database count and see if I can still duplicate the problem. Thanks. Justin Pasher -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Autovacuum daemon terminated by signal 11
Hello, I have a server running PostgreSQL 8.1.15-0etch1 (Debian etch) that was recently put into production. Last week a developer started having a problem with his psql connection being terminated every couple of minutes when he was running a query. When I look through the logs, I noticed this message. 2009-01-09 08:09:46 CST LOG: autovacuum process (PID 15012) was terminated by signal 11 I looked through the logs some more and I noticed that this was occurring every minute or so. The database is a pretty heavily utilized system (judging by the age(datfrozenxid) from pg_database, the system had run approximately 500 million queries in less than a week). I noticed that right before every autovacuum termination, it tried to autovacuum a database. 2009-01-09 08:09:46 CST LOG: transaction ID wrap limit is 4563352, limited by database database_name It was always showing the same database, so I decided to manually vacuum the database. Once that was done (it was successful the first time without errors), the problem seemed to go away. I went ahead and manually vacuumed the remaining databases just to take care of the potential xid wraparound issue. I figured it was just an isolated incident, until it started happening again this week. Same scenario as before: over 500 million queries since the beginning of this week and autovacuum dying on the same database every time. However, the problematic database was different than last time, so it doesn't seem to be specific to one particular database. Looking through the archives I've seen this (exact?) same problem crop up before, but it was addressed in Postgres 8.1.1 http://archives.postgresql.org/pgsql-bugs/2006-01/msg00014.php This article also mentioned the previous bug was related to triggers on the table, but the second time this happened to me, the database in question only has two simple tables (no triggers, one foreign key linking them, a few btree indices). What else can I do to determine the cause of this? For the time being, I should be able to setup a cron job to run a manual vacuum every other day to ensure that age(datfrozenxid) stays low, but I'd like to understand what would be causing this. Justin Pasher -- 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] [PGSQL 8.3.5] Use of a partial indexes
Reg Me Please wrote: Only one question remains in my mind: why the planner is not using the partial index? The partial index is covering 2 predicates out of the 3 used in the where condition. Actually there is a boolean flag (to exclude disabled rows), a timestamp (for row age) and an int8 (a FK to another table). The first two are in the partial index in order to exclude disabled and older rows. The int8 is the random key I mentioned earlier. So the WHERE condition reads like: flag AND tstz = '2008-01-01'::timestamptz and thekey=42 I can see in the EXPLAIN that there is no mention to the partial index. Please keep in mind that the table has 8+M rows, few of which are flagged, about 70% don't match the age limit and few dozens match the key. In my opinion the partial index should help a lot. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand For an index to be used the where clause must match the index. As the index gets more complicated its less likely to be used. I have 5 indexes on one table to answer the 5 possible ways the where clause can look like. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need some help with pl-pgsql
justin wrote: Adrian Klaver wrote: - justin jus...@emproshunts.com wrote: So it appears that something is causing _wipvalue to get set to NULL somewhere else in the code. Now when running the failing select statement manually works is because the modified record is still not committed yet and its reading the unmodified record. the ForceCloseWo() calls several other functions before PostProduction() that beat on the header record a few times. ForceCloseWo does extactly what it sounds like closes Work Orders forcible based on default values set in the database. I'm betting this record has some flaky setting somewhere thats either in the posting of raw material or labor operations. its late i'm going to bed fight this again later this morning. thanks Found the problem at last. it was what i thought early this morning, the database had some flaky data set to null. so modified the the tables in question set defaults to zero and updated all the records that has null to zero. hopefully that fixes the problem going forward. thanks to all for helping, pointing me in the right direction to figure out what is going on, as i was completely lost of what next to do.
[GENERAL] need some help with pl-pgsql
have a function written in pgsql it runs just fine, except its doing something really stupid. The function runs just fine till this select statement Select (wo_wipvalue/wo_qtyord) into _TotalCost from wo where wo_id = pWoid ; This goes to the work order header table to gets the current value divided by current qty thats been completed so far, then sticks the value into _TotalCost variable based on the parameter passed into variable pWoid. problem is it returns NULL which is impossible as i can manually run the select statement and get the $1.589445 I have no idea how to fix this problem going forward??? and the database is 8.3.5 running Ubuntu 8.0 lts -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need some help with pl-pgsql
Adrian Klaver wrote:
On Tuesday 23 December 2008 1:04:49 pm justin wrote:
have a function written in pgsql it runs just fine, except its doing
something really stupid.
The function runs just fine till this select statement
Select (wo_wipvalue/wo_qtyord)
into _TotalCost
from wo
where wo_id = pWoid ;
This goes to the work order header table to gets the current value
divided by current qty thats been completed so far, then sticks the
value into _TotalCost variable based on the parameter passed into
variable pWoid.
problem is it returns NULL which is impossible as i can manually run
the select statement and get the $1.589445
I have no idea how to fix this problem going forward???
and the database is 8.3.5 running Ubuntu 8.0 lts
Would help to see the whole function. Also make sure you did not name one of
the variables the same as a column name, this will confuse plpgsql. Are you
using the same value for wo_id in the function as in the manual select
statement?
First the funciton has been running for months and never has had a
problem. No changes to the database scheme. Second use variable naming
scheme completely different from column names. _ always is the first
character in variables. p is always the first character in passed
parameters.
Take a look at the screen shot and be in aw as i am
postproduction(pwoid integer, pqty numeric, pbackflush boolean,
pbackflushoperations boolean, pitemlocseries integer, psuuser text,
prnuser text, pdate date)
RETURNS integer AS
$BODY$ DECLARE
_woNumber TEXT;
_itemlocSeries INTEGER;
_parentQty NUMERIC;
_qty NUMERIC;
_TotalCost numeric;
BEGIN
IF (pQty = 0) THEN
RETURN 0;
END IF;
IF ( ( SELECT wo_status
FROM wo
WHERE (wo_id=pWoid) ) NOT IN ('R','E','I') ) THEN
RETURN -1;
END IF;
--If this is item type Job then we are using the wrong function
SELECT item_type INTO _check
FROM wo, itemsite, item
WHERE ((wo_id=pWoid)
AND (wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (item_type = 'J'));
IF (FOUND) THEN
RAISE EXCEPTION 'Work orders for job items are posted when
quantities are shipped on the associated sales order';
END IF;
SELECT formatWoNumber(pWoid) INTO _woNumber;
SELECT roundQty(item_fractional, pQty) INTO _parentQty
FROM wo,
itemsite,
item
WHERE ((wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (wo_id=pWoid));
-- Create the material receipt transaction
IF (pItemlocSeries = 0) THEN
SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
ELSE
_itemlocSeries = pItemlocSeries;
END IF;
--Lets get Wips Current total cost
Select (wo_wipvalue/wo_qtyord)
into _TotalCost
from wo
where wo_id = pWoid ;
--Moves WIP into Inventory.
SELECT postInvTrans( itemsite_id,
'RM',
_parentQty,
'W/O',
'WO',
_woNumber,
'',
'Receive Inventory from Manufacturing',
costcat_asset_accnt_id,
costcat_wip_accnt_id,
_itemlocSeries,
true,
_TotalCost,
pDate::timestamp ) INTO _invhistid
FROM wo,
itemsite,
costcat
WHERE ( (wo_itemsite_id=itemsite_id)
AND (itemsite_costcat_id=costcat_id)
AND (wo_id=pWoid) );
-- Increase this W/O's received qty decrease its WIP value
UPDATE wo SET
wo_qtyrcv = (wo_qtyrcv + _parentQty),
wo_wipvalue = (wo_wipvalue - (_TotalCost*pQty))
FROM itemsite,
item
WHERE ((wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (wo_id=pWoid));
-- Make sure the W/O is at issue status
UPDATE wo SET
wo_status='I'
WHERE (wo_id=pWoid);
RETURN _itemlocSeries;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION postproduction(integer, numeric, boolean, boolean,
integer, text, text, date) OWNER TO justin;
GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
boolean, integer, text, text, date) TO justin;
GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
boolean, integer, text, text, date) TO public;
Re: [GENERAL] need some help with pl-pgsql
Adrian Klaver wrote: On Tuesday 23 December 2008 3:36:46 pm Adrian Klaver wrote: Hmmm. A couple of suggestions while I ponder this more. For clarity sake decide on a case scheme. I start getting twitchy when I see pwoid and pWoid refer to the same thing. This depends on case folding being consistent, not something I like to count on. Second you might want to put in a few RAISE NOTICE statements to see what values are being pulled from the db for wo_wipvalue and wo_qtyord and what is being supplied to the WHERE clause for pWoid. Adrian Klaver akla...@comcast.net SELECT postInvTrans( itemsite_id, 'RM', _parentQty, 'W/O', 'WO', _woNumber, '', 'Receive Inventory from Manufacturing', costcat_asset_accnt_id, costcat_wip_accnt_id, _itemlocSeries, true, _TotalCost, pDate::timestamp ) INTO _invhistid Potential issue. I don't see where _invhistid is declared. its declared. i noticed this function has more variables declared than it ever used so i removed them when posting to the list. I did not want anymore confusion so I removed one to many sorry. This function was completely rewritten several months ago along with a allot of other functions so there still allot of clean up to do making notes and removing stuff that does not need to be in the function. I just can't figure out why in this function it can't find the record. This malfunctioning function is called by ForceCloseWo() which goes through the work order completing any outstanding operations, and issuing material so it has to find the record first or it will fail out right. Once all material and operations are marked completed it then calls PostProduction() which moves the items out of WIP tables put the items into Inventory and does all the accounting stuff. So i really can't figure out what it is doing??? Now On the mixed case. i come from the Xbase languages specifically Foxpro which is case insensitive and a typeless language so i have a few really bad habits. I try to get every thing to match but sometimes screw up.
Re: [GENERAL] need some help with pl-pgsql
Adrian Klaver wrote:
- justin jus...@emproshunts.com wrote:
Adrian Klaver wrote:
Would help to see the
whole function. Also make sure you did not name one of
the variables the same as a column name, this will confuse plpgsql.
Are you
using the same value for wo_id in the function as in the manual select
statement?
First the funciton has been running for months and never has had a
problem. No changes to the database scheme. Second use variable naming
scheme completely different from column names. _ always is the first
character in variables. p is always the first character in passed
parameters.
Take a look at the screen shot and be in aw as i am
postproduction(pwoid integer, pqty numeric, pbackflush boolean,
pbackflushoperations boolean, pitemlocseries integer, psuuser text,
prnuser text, pdate date)
RETURNS integer AS
$BODY$ DECLARE
_woNumber TEXT;
_itemlocSeries INTEGER;
_parentQty NUMERIC;
_qty NUMERIC;
_TotalCost numeric;
BEGIN
IF (pQty = 0) THEN
RETURN 0;
END IF;
IF ( ( SELECT wo_status
FROM wo
WHERE (wo_id=pWoid) ) NOT IN ('R','E','I') ) THEN
RETURN -1;
END IF;
--If this is item type Job then we are using the wrong function
SELECT item_type INTO _check
FROM wo, itemsite, item
WHERE ((wo_id=pWoid)
AND (wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (item_type = 'J'));
IF (FOUND) THEN
RAISE EXCEPTION 'Work orders for job items are posted when quantities
are shipped on the associated sales order';
END IF;
SELECT formatWoNumber(pWoid) INTO _woNumber;
SELECT roundQty(item_fractional, pQty) INTO _parentQty
FROM wo,
itemsite,
item
WHERE ((wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (wo_id=pWoid));
-- Create the material receipt transaction
IF (pItemlocSeries = 0) THEN
SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
ELSE
_itemlocSeries = pItemlocSeries;
END IF;
--Lets get Wips Current total cost
Select (wo_wipvalue/wo_qtyord)
into _TotalCost
from wo
where wo_id = pWoid ;
--Moves WIP into Inventory.
SELECT postInvTrans( itemsite_id,
'RM',
_parentQty,
'W/O',
'WO',
_woNumber,
'',
'Receive Inventory from Manufacturing',
costcat_asset_accnt_id,
costcat_wip_accnt_id,
_itemlocSeries,
true,
_TotalCost,
pDate::timestamp ) INTO _invhistid
FROM wo,
itemsite,
costcat
WHERE ( (wo_itemsite_id=itemsite_id)
AND (itemsite_costcat_id=costcat_id)
AND (wo_id=pWoid) );
-- Increase this W/O's received qty decrease its WIP value
UPDATE wo SET
wo_qtyrcv = (wo_qtyrcv + _parentQty),
wo_wipvalue = (wo_wipvalue - (_TotalCost*pQty))
FROM itemsite,
item
WHERE ((wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (wo_id=pWoid));
-- Make sure the W/O is at issue status
UPDATE wo SET
wo_status='I'
WHERE (wo_id=pWoid);
RETURN _itemlocSeries;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION postproduction(integer, numeric, boolean, boolean,
integer, text, text, date) OWNER TO justin;
GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
boolean, integer, text, text, date) TO justin;
GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
boolean, integer, text, text, date) TO public;
Hmmm. A couple of suggestions while I ponder this more. For clarity sake decide
on a case scheme. I start getting twitchy when I see pwoid and pWoid refer to
the same thing. This depends on case folding being consistent, not something I
like to count on. Second you might want to put in a few RAISE NOTICE statements
to see what values are being pulled from the db for wo_wipvalue and wo_qtyord
and what is being supplied to the WHERE clause for pWoid.
Adrian Klaver
akla...@comcast.ne
I have taken your idea and made sure all the variables all appear the
same and add raise notice for each portion of the command that is failing.
-
NOTICE: _wipvalue: NULL
CONTEXT: SQL statement SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )
PL/pgSQL function forceclosewo line 66 at SQL statement
NOTICE: _wipqty: 1.
CONTEXT: SQL statement SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )
PL/pgSQL function forceclosewo line 66 at SQL statement
NOTICE: _wipvalue/_wipqty= NULL
CONTEXT: SQL statement SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )
PL/pgSQL function forceclosewo line 66 at SQL statement
NOTICE: pwoid: 6916
CONTEXT: SQL statement SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )
PL/pgSQL function forceclosewo line 66 at SQL statement
NOTICE: TotalCost: NULL
CONTEXT: SQL statement SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )
PL/pgSQL function forceclosewo line 66 at SQL statement
So it appears that something is causing _wipvalue to get set to NULL
somewhere else in the code. Now
Re: [GENERAL] Syntax error with select statement
aravind chandu wrote: Hello, I have problem with select statement in c++ program I am using pqxx library to connect to postgresql database.My query is result R(T.exec( select * from dbtable where username = ' +user+ ' and password = ' +st+ ' )); here st is in encrypted format and the string is st = M^fuo|`sjyo|`so|-?z this is the string i stored in the table . The error I was encountered is terminate called after throwing an instance of 'pqxx::syntax_error' what(): ERROR: unterminated quoted string at or near 'M^fuo|`sjyo|`so|-?z LINE 1: ...table where username = 'achandana' and password = 'M^fuo|`sj... ^ I am not able to identify what the actual problem is can you guys please help to solve this problem?Your help is greatly appreciated. Thank You, Aravind Well its telling you in the error the quotes are flaky. It apears that the password portion contains another sing quote. I would move to double dollar quoting when dealing with strings that contain special characters example R(T.exec( select * from dbtable where username = $UserName$ + user + $Username$ and password = $Password$ + st + $Password$ )); see http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html on dollar quoting
[GENERAL] Eweek-Sun-Monty-MySQL
Eweek Article above is article on eweek discussing Monty's blog on the poor shape MySql 5.1 is in
Re: [GENERAL] returns numbers of record
Select Count(*) from (query) is what i believe you are looking for see http://www.postgresql.org/docs/8.3/interactive/tutorial-agg.html Enrico Pirozzi wrote: Hi all, does it exists a way to know how many records a query returns? I thought sometime like DECLARE curs1 CURSOR FOR select * from table ; I thought if I can write MOVE LAST Is there any way to return the number of row for select * from table without execute a count(*) ? Thanks :) Enrico -- 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] Monty on MySQL 5.1: Oops, we did it again
Geoffrey wrote: Grzegorz Jaśkiewicz wrote: On Mon, Dec 1, 2008 at 8:00 PM, Steve Crawford [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html All interesting, but especially the part about half-way down under the heading So what went wrong with MySQL 5.1 ? - must-read for anyone involved in selecting a database. well, at least they have replication and partitioning built in. How reliable it is, is completely another story - but still, they are a step ahead in that regard. Now I know why Tom Lane doesn't have a blog :) Actually, he has a couple of them: pgsql-general@postgresql.org [EMAIL PROTECTED] . . :) I'm very happy and proud to use Postgresql as the developers working on Postgresql deliver a quality product, not claim its quality. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Favorite Tom Lane quotes
Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] writes: We really need a favorite Tom Lane quotes thread. Mine is (roughly): We don't support that, but you're free to try it, you just get to keep both pieces if it breaks. Hate to disillusion you, but that's a standard comment around Red Hat. I have no idea who said it first, but twasn't me. i took this off Wikipedia http://en.wikipedia.org/wiki/Tom_Lane_(Open_Source_Software_Developer) On idiotic benchmark comparisons Try to carry 500 people from Los Angeles to Tokyo in an F-15. No? Try to win a dogfight in a 747. No? But they both fly, so it must be useful to compare them... especially on the basis of the most simplistic test case you can think of. For extra points, use *only one* test case. Perhaps this paper can be described as comparing an F-15 to a 747 on the basis of required runway length
Re: [GENERAL] Error in Adding All Tables
salman Sheikh wrote: Hi freinds, i wanted to add my all tables once in MFC application, i have a databank,which has 11 tables and i want to add them all togather. After Adding all tables, it shows me always this errors by debugging. ERROR: column reference "ctid" is ambiguous; Error while executing the query i need help from u ppl. I am using Visual C++ 2005 and postgresql 8.3. thanks Sheikh Pt! Schon vom neuen WEB.DE MultiMessenger gehoum l;rt? Der kann`s mit allen: http://www.produkte.web.de/messenger/?did=3123 Without looking at the sql statement you are using its kinda hard to know what the problem is but ambiguous column means the Postgresql can't figure out what you want to do as the column shows up twice in a command.
Re: [GENERAL] Error in Adding All Tables
I have no idea what you are trying to do?? please send the SQL commands you are using. For me to help you Need to know what you are trying to accomplish and need to see the code! salman Sheikh wrote: hi Do u have any suggestion regarding this problem? can u add all tables once in postgresql? In MS access i can add table once without any problem. sheikh Von: "justin" [EMAIL PROTECTED] Gesendet: 02.11.08 01:23:15 An: salman Sheikh <[EMAIL PROTECTED]> CC: pgsql-general@postgresql.org, [EMAIL PROTECTED] Betreff: Re: [GENERAL] Error in Adding All Tables salman Sheikh wrote: Hi freinds, i wanted to add my all tables once in MFC application, i have a databank,which has 11 tables and i want to a dd themall togather. After Adding all tables, it shows me always this errors bydebugging. ERROR: column reference "ctid" is ambiguous; Error while executing the query i need help from u ppl. I am using Visual C++ 2005 andpostgresql 8.3. thanks Sheikh Pt!Schon vom neuen WEB.DE MultiMessenger gehoum l;rt? Der kann`s mit allen: http://www.produkte.web.de/messenger/?did=3123 Without looking at the sql statement you are using its kinda hard toknow what the problem is but ambiguous column means the Postgresql can't figure o ut what youwant to do as the column shows up twice in a command. n 5 Schritten zur eigenen Homepage. Jetzt Domain sichern und gestalten! Nur 3,99 EUR/Monat! http://www.maildomain.web.de/?mc=021114
Re: [GENERAL] Error in Adding All Tables
you mean you are trying some kind of table joining correct??? example Select * from mytable, othertable where ctid = ctid ??? Please post the SQL statement you are using to communicate with the database. The select insert update commands. If i could see what you are doing i would have solved your problem 3 emails ago. just post the C++ code that has the problem salman Sheikh wrote: Hi i just want to add table through MFC application,in Visual C++ it generates all classes automatically,if i add table one by one,it shows no problem.i can comunicate with my database.But if i add 2 or more tables togather,then it shows this error, i dont know why? sheikh Von: "justin" [EMAIL PROTECTED] Gesendet: 02.11.08 01:48:04 An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Error in Adding All Tables I have no idea what you are trying to do?? please send the SQLcommands you are using. For me to help you Need to know what you are trying to accomplish and need to see the code! salman Sheikh wrote: hi Do u ha ve any suggestion regarding this problem? can u add all tables once in postgresql? In MS access i can add table once without any problem. sheikh Von: "justin" [EMAIL PROTECTED] Gesendet: 02.11.08 01:23:15 An: salman Sheikh CC: pgsql-general@postgresql.org,[EMAIL PROTECTED] Betreff: Re: [GENERAL] Error in Adding All Tables @WEB.DE salman Sheikh wrote: Hi freinds, i wanted to add my all tables once in MFC application, i have a databank,which has 11 tables and i want toa dd themall togather. After Adding all tables, it shows me always thiserrors bydebugging. ERROR: column reference "ctid" is ambiguous; Error while executing the query i need help from u ppl. I am using Visual C++ 2005andpostgresql 8.3. thanks Sheikh Pt!Schonvom neuen WEB.DE MultiMessenger gehoum l;rt? Der kann`s mit allen: http://www.produkte.web.de/messenger/?did=3123 Without looking at the sql statement you are using its kinda hardtoknow what the problem is but ambiguous column means the Postgresql can't figure o ut whatyouwant to do as the column shows up twice in a command. n 5Schritten zur eigenen Homepage. Jetzt Domain sichern und gestalten! Nur 3,99 EUR/Monat! http://www.maildomain.web.de/?mc=021114 er WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! http://smartsurfer.web.de/?mc=100071distributionid=0066
Re: [GENERAL] Error in Adding All Tables
Yes, i'm working with VS 2008 with QT framework working on a
application with i don't know couple hundred thousand lines of code.
Been using VS sense 1997 how about you???
One of your big problems is your using the Automatic Class builder.
Its sucks and is what is causing your problem as it creates code that
don't work.
Case in point the SQL code is wrong.
In this case the Select statement being created is trying to join two
tables together that have the same column name
Select * from mytable, othertable where ctid = ctid
this command will create the error you have
now to avoid the error it should look like this
Select * from mytable, othertable where mytable.ctid = othertable.ctid
Now Postgresql knows what you want it to do. Before the Postgresql has
two columns with the same name and does not understand how to join the
two tables
Find the the code where the Select statement is change the Joining
argument
salman Sheikh wrote:
Hi Justin
thanks for helping me,
But can i ask u ,have u ever worked with Visual C++ 2005.It does
every thing automatically,u just click on the button it will generats
all classes, for example View class, document class Set class and so
on,Set class is actually connection class,which connect application
with database, and shows all columns of the table which we added in
this application,thatwhy i dont need to write any sql code for adding
all table,how long i know,if i am wrong pls correct me.
thanks once more Jusitn
sheikh
Von: "justin" [EMAIL PROTECTED]
Gesendet: 02.11.08 02:02:21
An: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Error in Adding All Tables
you mean you are trying some kind of table joining correct???
example
Select * from mytable, othertable where ctid = ctid ???
Please post the SQL statement you are using to communicate with
thedatabase.
The select insert update commands. If i could see what you are doingi
would have solved your problem 3 emails ago.
just post the C++ code that has the problem
salman Sheikh wrote:
Hi
i just want to add table through MFC application,in Visual
C++ itgenerates all classes automatically,if i add table one by one,it
showsno problem.i can comunicate with my database.But if i add 2 or
moretables togather,then it shows this error, i dont know why?
sheikh
Von: "justin" justi
[EMAIL PROTECTED]
Gesendet: 02.11.08 01:48:04
An: [EMAIL PROTECTED]
Betreff: Re: [GENERAL] Error in Adding All Tables
I have no idea what you are trying to do?? please
send theSQLcommands you are using.
For me to help you
Need to know what you are trying to accomplish and need to see the code!
salman Sheikh wrote:
hi
Do u ha ve any suggestion regarding this problem?
can u add all tables once in postgresql?
In MS access i can add table once without any
problem.
sheikh
Von: "justin" [EMAIL PROTECTED]
/A
Gesendet: 02.11.08 01:23:15
An: salman Sheikh
CC: pgsql-general@postgresql.org,[EMAIL PROTECTED]
Betreff: Re: [GENERAL] Error in Adding
All Tables
@WEB.DE
salman Sheikh wrote:
Hi freinds,
i wanted to add my all tables once in MFC application,
i have a databank,which has 11
tables and iwant toa dd themall togather.
After Adding all tables, it shows
me alwaysthiserrors bydebugging.
ERROR: column reference "ctid" is ambiguous;
Error while executing the query
i need help from u ppl. I am using
VisualC++ 2005andpostgresql 8.3.
thanks
She ikh
Pt!Schonvom
neuenWE
Re: [GENERAL] Error in Adding All Tables
Exactly NO not a clue. Do a crtl+F which brings up the Search box in Visual Studio search for Select. it will be in one of automatically created .cpp files. Class builder is suppose to create fully qualified queries but in some cases it does not mshelpfile http://msdn.microsoft.com/en-us/library/s9ds2ktb%28VS.80%29.aspx the command will look something like DatabaseConnectionObject.ExecuteSql= Select column1, column2, column3 column4 from Mytable Left Join Othertable on ctid = ctid . it needs to look like this DatabaseConnectionObject.ExecuteSql = Select column1, column2, column3 column4 from Mytable Left Join Othertable on OtherTable.ctid = MyTable.ctid One method that is used by a number people is make sure columns in tables never share the same name. one way is use the first 3 letters of a table followed by an underscore then name of the column example ( oth_citd and myt_ctid) This way one never has to worry typing fully qualified names. This is a method i use when setting up tables. I strongly suggest learning SQL syntax. If you can wrap your mind around C++ and its quirks you can very quickly learn SQL Also you will need to learn http://www.postgresql.org/docs/current/static/plpgsql.html its very easy to pick up also and write complicated stored procedures to manipulated data Also take note what qualifies as SQL command in Access sometimes will not work in Postgresql Another thought is not use ODBC at all and use libpq http://www.postgresql.org/docs/8.3/interactive/libpq.html there more work involved. salman Sheikh wrote: HI justin actually i am newbie in this field,thatwhy i dont have much knowledge it. Now i understand the problem,but i am using MFC classes,as i told u it generates automatically, do u have any idea where should i change the code? i am very thankful to you man sheikh *Von:* justin [EMAIL PROTECTED] *Gesendet:* 02.11.08 02:34:44 *An:* pgsql-general@postgresql.org *Betreff:* Re: [GENERAL] Error in Adding All Tables Yes, i'm working with VS 2008 with QT framework working on aapplication with i don't know couple hundred thousand lines of code. Been using VS sense 1997 how about you??? One of your big problems is your using the Automatic Class builder. Its sucks and is what is causing you r problem as it creates code thatdon't work. Case in point the SQL code is wrong. In this case the Select statement being created is trying to join twotables together that have the same column name Select * from mytable, othertable where ctid = ctid this command will create the error you have now to avoid the error it should look like this Select * from mytable, othertable where mytable.ctid = othertable.ctid Now Postgresql knows what you want it to do. Before the Postgresql hastwo columns with the same name and does not understand how to join thetwo tables Find the the code where the Select statement is change the Joiningargument salman Sheikh wrote: Hi Justin thanks for helping me, But can i ask u ,have u ever worked with Visual C++ 2005.It doesevery thing automatically,u just click on the button it will generatsall classes, for example View class, document class Set class and soon,Set class is actually connection class,which connect applicationwith database, and shows all columns of the table which we added inthis application,thatwhy i dont need to write any sql code for addingall table,how long i know,if i am wrong pls correct me. thanks once more Jusitn sheikh *Von:* justin [EMAIL PROTECTED] *Gesendet:* 02.11.08 02:02:21 *An:* pgsql-general@postgresql.org *Betreff:* Re: [GENERAL] Error in Adding All Tables you mean you are trying some kind of table joining correct??? example Select * from mytable, othertable where ctid = ctid ??? Please post the SQL statement you are using to communicate withthedatabase. The select insert update commands. If i could see what you are doingiwould have solved your problem 3 emails ago. just post the C++ code that has the problem salman Sheikh wrote: Hi i just want to add table through MFC application,in VisualC++ itgenerates all classes automatically,if i add table one by one,itshowsno problem.i can comunicate with my database.But if i add 2 ormoretables togather,then it shows this error, i dont know why? sheikh *Von:* justin [EMAIL PROTECTED] *Gesendet:* 02.11.08 01:48:04 *An:* [EMAIL PROTECTED] *Betreff:* Re: [GENERAL] Error in Adding All Tables I have no idea what you are trying to do?? pleasesend theSQLcommands you
Re: [GENERAL] Are there plans to add data compression feature to postgresql?
小波 顾 wrote: Data Compression MSSQL 2008 technots . Your results depend on your workload, database, and hardware Sounds cool but i wonder what real world results are?? For IO bound systems lots of pluses but for CPU bound workloads it would suck
Re: [GENERAL] Group BY and Chart of Accounts
There was a number of code mistakes in my examples as i was just doing it off the top of my head, just went through it and got it all working. I had to change the function around as it was double dipping accounts just run this and it does work. -- Create table coa ( coa_id serial not null, parent_id int not null default 0, doIhaveChildren boolean default false, account_name text null ); Create Table general_ledger_transactions( transaction_id serial not null, coa_id integer, accounting_period integer, debit numeric(20,10) , credit numeric(20,10), transaction_date timestamp); Create table accounting_periods ( accounting_period serial not null, start_date date, end_date date, accounting_period_Open boolean); Insert into coa values (10, default, True, 'ParentAccount1'); Insert into coa values (11, 10, True, 'ChildAccount1'); Insert into coa values (12, 11, false, 'ChildAccount2'); Insert into coa values (13, default, false, 'ChildAccount3'); Insert into Accounting_Periods values ( 1, '2008-10-01', '2008-10-31', true ); Insert into Accounting_Periods values ( 2, '2008-11-01', '2008-11-30', true ); Insert into general_ledger_transactions values( default, 11, 1, 30.0, 0.0, current_timestamp); Insert into general_ledger_transactions values( default, 11, 1, 20.0, 0.0, current_timestamp); Insert into general_ledger_transactions values( default, 12, 1, 10.0, 0.0, current_timestamp); Insert into general_ledger_transactions values( default, 12, 1, 50.0, 0.0, current_timestamp); Insert into general_ledger_transactions values( default, 11, 1, 1.0, 0.0, current_timestamp); Insert into general_ledger_transactions values( default, 13, 1, 0.0, 111.0, current_timestamp); Insert into general_ledger_transactions values( default, 11, 2, 0.0, 30.0, current_timestamp); Insert into general_ledger_transactions values( default, 11, 2, 0.0, 20.0, current_timestamp); Insert into general_ledger_transactions values( default, 12, 2, 0.0, 10.0, current_timestamp); Insert into general_ledger_transactions values( default, 12, 2, 0.0, 50.0, current_timestamp); Insert into general_ledger_transactions values( default, 11, 2, 0.0, 1.0, current_timestamp); Insert into general_ledger_transactions values( default, 13, 2, 111.0, 0.0, current_timestamp); CREATE OR REPLACE FUNCTION GetChildAccountDebits(PassedAccountID integer, PassedPeriodID integer) RETURNS NUMERIC AS $FunctionCode$ DECLARE retval NUMERIC = 0.0 ; begin return (SELECT coalesce ( (select Sum(general_ledger_transactions.debit ) from general_ledger_transactions where general_ledger_transactions.coa_id = coa.coa_id and general_ledger_transactions.accounting_period = PassedPeriodID), 0 ) + (CASE WHEN coa.doIhaveChildren THEN GetChildAccountDebits(coa.coa_id, PassedPeriodID ) ELSE 0.0 END) FROM coa WHERE coa.parent_id = PassedAccountID); end; $FunctionCode$ LANGUAGE 'plpgsql' VOLATILE ; select 10, getchildaccountdebits(10,1) union select 11, getchildaccountdebits(11,1) union select 12, getchildaccountdebits(12,1); -- WaGathoni wrote: Justin was recommending a solution to the Chart of Accounts Problem posted by jamhitz: MQUOTE One has you chart of Accounts Create table coa ( coa_id serial not null, parent_id int not null default 0, doIhaveChildren boolean default false account_name text null ) primary key(coa_id) Create Table general_ledger_transactions( transaction_id serial not null coad_id integer, accounting_period integer, debit numeric(20,10) , credit numeric(20,10), transaction_date datestamp) primary key (transaction_id) ... Create table accounting_periods ( accounting_period serial not null, start_date date, end_date date, accounting_period_Open boolean) /QUOTE Would someone please assist me. Why is the following function:... CREATE OR REPLACE FUNCTION GetChildAccountDebits(PassedAccountID integer, PassedPeriodID integer) RETURNS NUMERIC AS $FunctionCode$ DECLARE retval NUMERIC :=0.0; begin SELECT Sum(gl_transactions.debit) + CASE WHEN coa.doIhaveChildren THEN GetChildAccountDebits(coa.coa_id, PassedPeriodID ) ELSE 0.0 END INTO retval FROM gl_transactions, coa WHERE gl_transactions.coa_id= coa.coa_id AND coa.parent_id = PassedAccountID AND gl_transactions.period_id = PassedPeriodID; RETURN retval; end; $FunctionCode$ LANGUAGE 'plpgsql' VOLATILE ; failing with an error to the effect that that that coa.doaIhaveChildren and coa.coa_id must be included in the GROUP BY clause and what is is the recommended course of action. I
Re: [GENERAL] Shopping cart
Andrus wrote: Thomas, http://www.satchmoproject.com/ But it is written in python. Not PHP or C#. Thank you. Unfortunately for me it seems that learning another language, Python + Django to support shopping cart is too much. Where to find Visual FoxPro, PHP or C#/mod_mono scripts for this? Those languages are widely used and must also contain such common thing. I have looked some PHP source code archives without success yet. Andrus. If you are looking for Visual Foxpro and C# shopping carts take a look at www.west-wind.com its not free code but its not a ripe off prices you normally see a few hundred bucks for the source code Also has a C# version in .net 2.0 and 1.1 plus it uses ODBC so it will work pretty much work with any database draw back is windows only and IIS. some people have gotten Apache to work with Foxpro version -- 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] Chart of Accounts]
James Hitz wrote: As I said earlier, I am quite green with PGSQL, so please bear with me when I ask Stupid questions... --- On Mon, 13/10/08, justin [EMAIL PROTECTED] wrote: I just redid the accounting side of an application we have access to source code, so been here and done this. If i was not for the rest of the application i would have completely redone the accounting table layout something like this Ok with the tables I would used views and the application to create the tree list view i think your after. As you also need to know the Open Balances, Debit, Credits and Closing Balances by accounting period.. One idea is is create a functions that scans through the general_ledger_transactions table to get your values So create a View something like this Example would by Select Sum(debits) + Case when coa.doIhaveChildren then GetChildAccountDebits(coa.coa_id, period_id) else 0.0 end; from general_ledger_transactions, coa, where general_ledger_transactions.coad_id = coa.coa_id and coa.coa_id = SomPassedAccountID group by general_ledger_transactions.period_id, general_ledger_transactions.coa_id I start getting lost : SomPassedAccountID ??? Where is this coming from? I put this in so the select statement would be limited to a specific account the user would choose from the UI it can be left out, it just would get all the accounts grouped by accounting period. I added to the group by clause the coad_id so it would not sum all the accounts as just one value. PassedPeriodID ??? ...and this? I hope this clarifies things Create or replace Function GetChildAccountDebits(PassedAccountID integer, PassedPeriodID integer) returns numeric as $FunctionCode$ begin return Select Sum(debits) + Case when coa.doIhaveChildren then GetChildAccountDebits(coa.coa_id, PassedPeriodID ) else 0.0 end; from general_ledger_transactions, coa, where general_ledger_transactions.coa_id= coa_id and coa.parent_id = PassedAccountID and general_ledger_transactions.period_id = PassedPeriodID ; end; $FunctionCode$ LANGUAGE 'plpgsql' VOLATILE ; Same as above one would normally limit account balances by accounting Period so only the values posted to that period show up. Also note Some people have 12 accounting periods aka calendar year others have 13 accounting periods 52 weeks in a year 4 weeks to an accounting period. = 13 periods Also fiscal http://en.wikipedia.org/wiki/Fiscal_yearyears don't have to match to calendar years this is the reason why accounting periods must be identified somehow to group transactions by period. This creates a loop back which can be dangers if Parent_account is also a Child_account of itself which creates an endless loop then creates a stack error. I think this is easy enough to control with a CHECK constraint I think. Otherwise, I see the sense in using two columns for transactions - If I were writing an application for a bank, then using one column only may have potential pitfalls. The difference between one column or two columns is personal preference like allot things. I prefer two columns as it makes more logical sense to me to split it out. But at presently i'm stuck using a system that uses One column in the gl table. :-(
Re: [GENERAL] Chart of Accounts
because a credit account is a liability account aka a negative account so credit a credit account causes it to go UP not down. Look a your bank statement it says Credit you $500 when you make a deposit its a debit to you a credit to the bank in a credit account as its a liability to the bank. to be way over general Credits are negative entries and Debits are positive entries. Another Way to think about it is Are you Exporting or Importing, it depends on which side of the equations you are on. When ever i try to explain importing and exporting to the accountants its my sweet revenge :-). Isak Hansen wrote: On Mon, Oct 13, 2008 at 2:57 AM, justin [EMAIL PROTECTED] wrote: [...] Also you want to split out the debit and credits instead of using one column. Example one column accounting table to track values entered how do you handle Crediting a Credit Account Type. is it a negative or positive entry??? How is crediting a credit account different from crediting any other account? YMMV, but I think a single amount column makes for a more consistent design.
Re: [GENERAL] Chart of Accounts
Gregory Stark wrote: justin [EMAIL PROTECTED] writes: special note do not use only 2 decimal points in the accounting tables. If your application uses 10 decimal places somewhere then every table in the database that has decimals needs to have the same precision. Nothing is more annoying where a transaction says 1.01 and the other side says 1.02 due to rounding. FWIW I think this is wrong. You need to use precisely the number of decimal places that each datum needs. If you use extra it's just as wrong as if you use too few. For example, when you buy gas/petrol at $1.999/gallon and buy 4 gallons you get charged $8.00 not $7.996. If you fail to round at that point you'll find that your totals don't agree with the amount of money in your actual bank account. I agree to a point. just went through this with our application and had total fits with compound rounding errors as one table stored 4 other stored 6 and 8 and the general ledger table stored 2. when it came time to balance the transactions to the General Ledger Entries we where off thousands of dollars in different accounts as the GL almost always was higher due to rounding and it was wrong to the detail side. The entire database uses the same precession as a whole then rounded on the display side.In our Case we make parts that consume .000113 lbs of a metal that sales for 25.76 a pound = 0.002911. When the transaction to remove the value from the inventory account in the Generial ledger table has an entry 0.00 not 0.002911. We just had to big discussion on this thread about rounding and precession which i kicked off.
Re: [GENERAL] Chart of Accounts
Craig Bennett wrote: If you want to take a particular system out to extra digits, it's probably good to record the rounding error as a separate component of the transaction (that is, if you want everything to balance out perfectly). I think you have two different problems here. On the one hand you have rounding errors which are material when aggregated on the other hand most sales transactions (for example) will come to a dollar and cents figure. If you have two accounts with different precision then I think from an accounting perspective you need to say something like this when posting between the two: DR My 2 Decimal Precision Account 2.00 DR Accumulated Rounding (4 Decimal) 0.0010 CROriginal 4 Decimal Account 2.0010 Then at period end you can including your rounding account and everything will balance. Craig Thats not the problem its the different tables having different precision. We have a WIP tables that notes all the labor and material consumed by all the jobs for an accounting period. So you have some jobs all ways open crossing periods so you need to audit that WIP process account which means going to the WIP tables and verifying that the values in the WIP account equal to the jobs in the WIP tables. If the detail differs even a a penny you have a problem you are not allowed to simply call it rounding error. Pushing it into another account called rounding error does not solve the problem. Values in the wip tables need to equal the values in the General ledger tables The problem occurs when the WIP tables store 6 and 8 decimals and the GL tables have only 2. it creates all kinds of rounding problems and it gets worst when you have thousands of transactions a day a penny multiplied by 1000 becomes 10 bucks times 30 days in a accounting period = 300 bucks. Thats getting pretty big for a rounding mistake and this is only one account. Now take that and multiply that by 10 accounts each going every which way. -- 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] Chart of Accounts
There are a couple of ways to solve your problem Heres my thoughts off the top of my head and what little i know about auctions and how they are run. Also i hope the formating comes out. please note these table do not contain all columns i would have in them its just an idea of how i would get all the tables linked together and laid out. Create Table contact ( contact_id serial not null , first_name text, last_name text, phone text, email text, company_name text, amIaCustomer boolean, amIaVendor boolean) Create Table AuctionHeader( action_id serial not null, date_to_have_action date, date_to_end_action date, auction_description text, auction_percent_take_for_each_item_sold numeric (10, 8) ) Create Table AuctionItems ( auction_id integer, item_id serial not null, item_description text, start_bid money, dont_sell_itemprice money, sold_price money, vendor_id integer, who_Brought_id integer, other_notes_ text) Create table InvoiceHeader ( invoice_id serial not null, item_id integer, vendor_id integer, customer_id integer, invoice_posted_to_gl boolean invoice_paid boolean payment_terms integer, invoice_issue_date date Payment_method text (Credit Card, Money, Check) ) Create Table AR_Header ( account_receivable_id serial not null invoice_id, invoice_total money, date_created date, notes text,) Create Table AR_PaymentsReceived ( ar_item serial not null, account_receivable_id integer, payment_method text, amount_received money, date_received date) Create Table InvoiceItems( item_id serial not null, sold_price money, actual_price_paid money) Create Table general_ledger_transactions( transaction_id serial not null reference_type character, (Am i a Invoice, JE, Credit Memor, Debit Memo, Inventory ) reference_id integer, ( the primary key to the reference table) journal_entry_id integer, (this is used to keep transctions that linked to together like You have debit and Credit account and some Journal Entries may hit 100 accounts ) coa_id integer, accounting_period integer, debit numeric(20,10) , credit numeric(20,10), transaction_date datestamp) primary key (transaction_id) ) When An item is sold by the auctioneer sold and an invoice is Created you would sum up the values Put a Debit to Vendors Account into the GL then Credit the Customer Owes Me Account, then when the money is collected Debit the Customer Owes Me Account credit into a Revenue Account. the gl transactions for the Invoice Creation could look like this TransAtion_id --- Ref_type Reference_id Jorunal_ID---Coa_id - debit--Credit 5784 Invoice Invoice: 785 78485 54 aka CustomerOwesMe$25 5785 Invoice Invoice: 785 78485 67 aka I owe Vendor$20 5786 Invoice Invoice: 785 78485 15 aka Money I could be making $5 5787 ARAR: 4785 78486 5 aka CustomerOwesMe $25 5788 ARAR: 4785 78486 25 aka BillPaidAccount $25 Then Simple selects with joins and a few Case statements can get everything linked together. Also note i am not an accountant by any imagination what so ever. all my stuff is reviewed by CPA and an in house accountant to make sure i get all the debits and credits correct Jeff Williams wrote: Hi Justin I like your method. A question I am in the process of developing an piece of auction software. How would you handle all the bidders and vendors so they all come from a table called contacts and have a serial number. Each Purchase/Payment needs to recorded against each contact as well in the general ledger. We need to get daily balances about each contact. Regards Jeff WIlliams Australia - Original Message - From: justin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Date: Sun, 12 Oct 2008 20:57:59 -0400 Subject: Re: [GENERAL] Chart of Accounts You are making this far to complicated. I just redid the accounting side of an application we have access to source code, so been here and done this. If i was not for the rest of the application i would have completely redone the accounting table layout something like this 3 Accounting Tables One has you chart of Accounts Create table coa ( coa_id serial not null, parent_id int not null default 0, doIhaveChildren boolean default false account_name text null ) primary key(coa_id) Create Table general_ledger_transactions( transaction_id serial not null coad_id integer, accounting_period integer, debit
Re: [GENERAL] Chart of Accounts
You are making this far to complicated. I just redid the accounting side of an application we have access to source code, so been here and done this. If i was not for the rest of the application i would have completely redone the accounting table layout something like this 3 Accounting Tables One has you chart of Accounts Create table coa ( coa_id serial not null, parent_id int not null default 0, doIhaveChildren boolean default false account_name text null ) primary key(coa_id) Create Table general_ledger_transactions( transaction_id serial not null coad_id integer, accounting_period integer, debit numeric(20,10) , credit numeric(20,10), transaction_date datestamp) primary key (transaction_id) special note do not use only 2 decimal points in the accounting tables. If your application uses 10 decimal places somewhere then every table in the database that has decimals needs to have the same precision. Nothing is more annoying where a transaction says 1.01 and the other side says 1.02 due to rounding. Also you want to split out the debit and credits instead of using one column. Example one column accounting table to track values entered how do you handle Crediting a Credit Account Type. is it a negative or positive entry??? Create table accounting_periods ( accounting_period serial not null, start_date date, end_date date, accounting_period_Open boolean) I would used views and the application to create the tree list view i think your after. As you also need to know the Open Balances, Debit, Credits and Closing Balances by accounting period.. One idea is is create a functions that scans through the general_ledger_transactions table to get your values So create a View something like this Example would by Select Sum(debits) + Case when coa.doIhaveChildren then GetChildAccountDebits(coa.coa_id, period_id) else 0.0 end; from general_ledger_transactions, coa, where general_ledger_transactions.coad_id = coa.coa_id and coa.coa_id = SomPassedAccountID group by general_ledger_transactions.period_id What happen is the GetChildAccountDebits() function takes two parameters. One is the coa_id and the other is accounting period to search The function would look something like this return Select Sum(debits) + Case when coa.doIhaveChildren then GetChildAccountDebits(coa.coa_id, period_id) else 0.0 end; from general_ledger_transactions, coa, where general_ledger_transactions.coa_id= coa_id and coa.parent_id = ThePassedAccountID and general_ledger_transactions.period_id =PassedPeriodID This creates a loop back which can be dangers if Parent_account is also a Child_account of itself which creates an endless loop then creates a stack error. Outside of that is works great. i do something very similar Bill of Material and in our Accounting James Hitz wrote: Dear All, I have just started experimenting with PGSQL, with a view to migrate from the SQL server I use currently. I am trying to implement an intelligent Chart of Accounts for an accounting program. The following is long-winded but please bear with me: I have a table coa (chart of accounts) with the following schema CREATE TABLE coa( coa_id serial not null, parent_id int not null default 0, account_name text not null, amt money default 0, primary key(coa_id) ); After populating the database with basic accounts it resembles this (the hierarchy is mine): coa_id, parent_id, account_name, amt 0,-1, 'Chart of Accounts',0.00 1, 0, 'Assets',0.00 5, 1, 'Fixed Assets',0.00 6, 5, 'Motor Van', 0.00 --truncated --- 2, 0, 'Liabilities', 0.00 3, 0, 'Income', 0.00 4, 0, 'Expenses',0.00 So far, so good. I would like it so that if the amt of a a child account changes, the parent account is updated, if a child account is deleted, the amount is reduced off of the parent account etc. I have managed to achieve this using the following trigger functions: CREATE OR REPLACE FUNCTION public.coa_del_amt() RETURNS trigger AS $body$ begin update coa set amt = amt - old.amt where coa_id = old.parent_id; return old; end; $body$ LANGUAGE 'plpgsql' -- CREATE OR REPLACE FUNCTION public.coa_ins_amt() RETURNS trigger AS $body$ begin UPDATE coa SET amt = amt + new.amt WHERE coa_id = new.parent_id; return new; end; $body$ LANGUAGE 'plpgsql' CREATE OR REPLACE FUNCTION public.coa_upd_amt() RETURNS trigger AS $body$ begin IF new.parent_id = old.parent_id THEN UPDATE coa SET amt = amt + (new.amt -
Re: [GENERAL] localhost (windows) performance
johnf wrote: I have a friend I asked to load postgres 8.3 on his XP machine. He then tested a python script which accesses several tables and discovered that it retrieves data very slowly. It takes about 20 seconds to retrieve the data - on localhost. However, using a remote connection to a postgres database (mine) over the internet and running the same python script it takes only 12 seconds. The difference of 8 seconds makes no sense - the remote is completely on the other coast. I then thought it had something to do with the data -although the data set is small. I did a complete dump and restored on his machine and again got the same results. The machine has a recent motherboard with 2 gb of ram. It does not appear to be swapping out ram. Using my local XP (accessing the LINUX database on the LAN) runs the same python script in just under 3 seconds (most of the time is in loading the GUI). One other major difference is I'm running postgres8.2 on linux. Anybody, have a suggestion - I'm not a windows guru. Or is this normal for windows? Hardware related i'm betting. The client is having to run the python the gui and postgresql on the same hard drive its getting IO bound What size is the data set 1 meg 20 megs or 100 megs. I have seen small record counts but it was nothing but blobs in the table so the table was 5 gigs. What is the hardware specs on the XP machine?? Can you post the Select statements??? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how can I find out the numeric directory name of each database in PostgreSQL 8.3
Hi, In PostgreSQL 7.x, I can use SQL: select datname, oid from pg_database to find out the numeric directory name under $PGDATA/base for each database. But it doesn't work for PostgreSQL 8.3. Is there any way I can do it for 8.3? Thanks, -- Justin Yao -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how can I find out the numeric directory name of each database in PostgreSQL 8.3
nothing special, just curious about it. I suppose it should be able to be located by SQL. Justin Tino Wildenhain wrote: Hi, Justin Yao wrote: Hi, In PostgreSQL 7.x, I can use SQL: select datname, oid from pg_database to find out the numeric directory name under $PGDATA/base for each database. But it doesn't work for PostgreSQL 8.3. Is there any way I can do it for 8.3? What would you do with that name once you have it? Tino -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how can I find out the numeric directory name of each database in PostgreSQL 8.3
forget it. I am really sorry about that. it works for me, too. when I did : dbname= \d pg_database Table pg_catalog.pg_database Column | Type| Modifiers ---+---+--- datname | name | not null datdba| oid | not null encoding | integer | not null datistemplate | boolean | not null datallowconn | boolean | not null datconnlimit | integer | not null datlastsysoid | oid | not null datfrozenxid | xid | not null dattablespace | oid | not null datconfig | text[]| datacl| aclitem[] | Indexes: pg_database_datname_index UNIQUE, btree (datname), tablespace pg_global pg_database_oid_index UNIQUE, btree (oid), tablespace pg_global Triggers: pg_sync_pg_database AFTER INSERT OR DELETE OR UPDATE ON pg_database FOR EACH STATEMENT EXECUTE PROCEDURE flatfile_update_trigger() Tablespace: pg_global I didn't find the Column oid, I take granted to think it may not work, but I didn't issue a command to have a try. Really sorry about that. But the question is, why there's no column named oid and it still works? Thanks, Justin Tom Lane wrote: Justin Yao [EMAIL PROTECTED] writes: In PostgreSQL 7.x, I can use SQL: select datname, oid from pg_database to find out the numeric directory name under $PGDATA/base for each database. But it doesn't work for PostgreSQL 8.3. It works for me ... what problem are you having? 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] how can I find out the numeric directory name of each database in PostgreSQL 8.3
thanks so much! Tom Lane wrote: Justin Yao [EMAIL PROTECTED] writes: But the question is, why there's no column named oid and it still works? \d doesn't show system columns. http://www.postgresql.org/docs/8.3/static/ddl-system-columns.html 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] Automated Backup On Windows
Greg Smith wrote: On Mon, 8 Sep 2008, justin wrote: I would have added it to the postgresqldocs.org webstie but can't create an account for my self.Tried following theses instructions http://www.postgresqldocs.org/wiki/Automated_Backup_on_Windows but never could get it to work That page has been moved to http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows and you shouldn't have a problem getting an account there. The postgresqldocs site ended up being temporary and shutdown once the official Wiki was available. Well I still can't create an account all it says is Login no create account button or screen anywhere? -- 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] Automated Backup On Windows
Dave Page wrote: On Tue, Sep 9, 2008 at 1:32 PM, justin [EMAIL PROTECTED] wrote: Greg Smith wrote: On Mon, 8 Sep 2008, justin wrote: I would have added it to the postgresqldocs.org webstie but can't create an account for my self.Tried following theses instructions http://www.postgresqldocs.org/wiki/Automated_Backup_on_Windows but never could get it to work That page has been moved to http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows and you shouldn't have a problem getting an account there. The postgresqldocs site ended up being temporary and shutdown once the official Wiki was available. Well I still can't create an account all it says is Login no create account button or screen anywhere? See the bottom section of the front page: http://wiki.postgresql.org/ It might actual help if i read the page :-[ . I kept looking for CREATE ACCOUNT next to login not at the bottom of the page.
Re: [GENERAL] Automated Backup On Windows
how does this page look to you guys/gals. I have never added anything to a WIKI before so any comments?? I left the original author stuff untouched my edit is appended to the top -- 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] Automated Backup On Windows
sorry forgot the link http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows justin wrote: how does this page look to you guys/gals. I have never added anything to a WIKI before so any comments?? I left the original author stuff untouched my edit is appended to the top -- 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] Automated Backup On Windows
Please use subsections to separate both methods. How about adding the page to the Windows category? I just fixed both those. Justin, you might want to look at how I reformatted that to get an idea what the usual style is like. Also, people who write whole articles or large sections are sometimes credited there, you might want to add yourself to the author list for that page at http://wiki.postgresql.org/wiki/Database_Administration_and_Maintenance Thanks i was going to take this Alvaro suggestion and clean it up more. Also it'd be good to mention that PGPASSWORD and .pgpass work with either method ... I put a stub page in at http://wiki.postgresql.org/wiki/Pgpass to cover this whole area but never really filled it in. I'd prefer seeing that get fleshed out and then the automation page can just link to it, because this is a very FAQ. I've been trying to figure out how to get pgpass to work on windows with no luck as of yet. If i ever figure out and test pgpass on several other windows version then i'll do up a documentation on pgpass. There is some conflicting instructions on pgpass. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
