Re: [GENERAL] timestamps, formatting, and internals
Jasen Betts wrote: On 2012-05-29, David Salisbury wrote: On 5/27/12 12:25 AM, Jasen Betts wrote: The query: "show integer_datetimes;" should return 'on' which means timestamps are microsecond precision if it returns 'off' your database was built with floating point timstamps and equality tests will be unreliable, I find that rather interesting. I was told that I was losing microseconds when I extracted an epoch from the difference between two timestamps and casted that value to an integer. So if I have integer timestamps ( your case above ) I get microseconds, but integer epochs is without microseconds? yeah, the microseconds appear as fractions of seconds, so in the conversion to integer epoch they get rounded off. I think you need to consider what you're actually computing and measuring. My understanding is that Meeus's Equation of Time calculation is good to something like 250mSec so that's the limit of your accuracy, but as soon as you start taking refraction and atmospheric turbulence into account- even with the Sun high above the horizon- you're going to degrade that. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SELECT issue with references to different tables
Hi Alex, I think would be better to reorganise model to awoid NULLs i.e. to includ new table: owners -owner_id -owner_name -ownertype (person/comapny) and have yours person_details table... and comapny_details_table... related 1:1 to owner_id However, solution for your way I think would be: SELECT * from pets_reference main JOIN pets ON (refid_pets = pets.id) LEFT JOIN persons ON (refid_persons = persons.id) LEFT JOIN companies ON (refid_companies = companies.id) WHERE ownersince = (SELECT MAX(ownersince) FROM pets_reference child WHERE child.ref_petid = main.ref_petid) Kind Regards, Misa 2012/6/2, Alexander Reichstadt : > Hi, > > I have a query I cannot figure out in postgres or actually in any other way > than using the client front end, which I would prefer not to do. > > So, I have 4 tables > > pets > persons > companies > pets_reference > > pets have owners, the owner at any point in time is either a persons or a > company, never both at the same time. > > So, the pets_reference table has the fields: > > refid_petsmatching table pets, field id > refid_persons matching table persons, field id > refid_companies matching table companies, field id > ownersincewhich is a timestamp > > A pet owner can change to persons A, resulting in a record in pets_reference > connecting pet and person with a timestamp, setting refid_companies to zero > and refid_persons to person A's record's id value. If the owner changes to > some other person B, then another record is added to pets_reference. Or if > the owner for that pet changes to a company, then a new record is added with > refid_persons being zero and refid_companies being the id value of that > companies id field value. So at the end of the day pets_reference results in > a history of owners. > > Now, the problem is with displaying a table with pets and only their current > owners. I can't figure out two things. > For one it seems I would need to somehow build a query which uses an if-then > branch to check if companies is zero or persons is zero to ensure to either > reference a persons or a companies record. > The second issue is that I only need the max(ownersince) record, because I > only need the current owner and not past owners. > > I toyed around with DISTINCT max(ownersince) and GROUP BY, but it only > results in errors. I am not the SQL guru, I know my way around so far and am > learning, but this is kind of another league and I can't really show any > good results I've come up with so far. Please, can someone help? > > Thanks > Alex > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- 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] Updateable Views or Synonyms.
> > what sorts of operations do synonyms need to support? > truncate? > alter table? > reindex? > I am no expert or anything but I would think they would be like symlinks so yes to all of the above. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] evaluating subselect for each row
As part of anonymizing some data, I want to do something like: update foo set bar = (select bar2 from fakes order by random() limit 1); But of course, that sets them all to the same value, whereas I want them all different. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] TYPE TABLE OF NUMBER
we are doing mgration activity form oracle to postgresql 9.1 How to convert this type : ORACLE : CREATE OR REPLACE TYPE "NUMBER_ARRAY" IS TABLE OF NUMBER please let me know how we can achive this in postgresql My understanding POSTGRESQL : CREATE TYPE "NUMBER_ARRAY" AS (arr int); Appreciate any help ! -- View this message in context: http://postgresql.1045698.n5.nabble.com/TYPE-TABLE-OF-NUMBER-tp5710983.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] evaluating subselect for each row
On Jun 3, 2012, at 10:55 AM, Scott Ribe wrote: > As part of anonymizing some data, I want to do something like: > > update foo set bar = (select bar2 from fakes order by random() limit 1); it may or may not be an option, but "update foo set bar=md5(bar)" is a pretty simple way to redact data. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] RLS & SEpgsql
Hello, I have a question about row level security and SEpgsql. As far as I can tell the current state is that the leaky view problem is solved and some kind of security checking provider (implemented for SElinux) exists. Row level security seems to be an additional step. Is it planned for 9.3dev? If yes, will this work on windows by default or do I need to write a custom security checking provider? Or will there be some kind of simple default provider which you can configure in some way? Right now I am emulating some simple form of RLS via an intarray field attached to each row which stores the permissions necessary to read this row. Thanks, -Matthias -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SELECT issue with references to different tables
Hi Misa, I have such a construct with kind of an auxiliary table that is only there to keep the current status. I made good experiences with it and the overhead of maintaining an additional table for a current status alongside a table for tracking a historical buildup is negligable and works pretty well. In this other case however I also need to do some calculations and derive amounts of items in stock, prices and so forth. It was rather a gut feeling to have this dual-table thing in said use case. But in this current scenario it seemed I simply lacked SQL practice to achieve this. Your one-shot query works perfectly, your speaking naming convention is self-explaanatory in its transparence. Thank you, and also thank you to all others who responded. Alex Am 03.06.2012 um 12:00 schrieb Misa Simic: > Hi Alex, > > I think would be better to reorganise model to awoid NULLs i.e. to > includ new table: > > owners > -owner_id > -owner_name > -ownertype (person/comapny) > > and have yours person_details table... and comapny_details_table... > related 1:1 to owner_id > > However, solution for your way I think would be: > > SELECT * from pets_reference main JOIN pets ON (refid_pets = pets.id) LEFT > JOIN persons ON (refid_persons = persons.id) LEFT JOIN companies ON > (refid_companies = companies.id) > WHERE ownersince = (SELECT MAX(ownersince) FROM pets_reference child > WHERE child.ref_petid = main.ref_petid) > > Kind Regards, > > Misa > > > 2012/6/2, Alexander Reichstadt : >> Hi, >> >> I have a query I cannot figure out in postgres or actually in any other way >> than using the client front end, which I would prefer not to do. >> >> So, I have 4 tables >> >> pets >> persons >> companies >> pets_reference >> >> pets have owners, the owner at any point in time is either a persons or a >> company, never both at the same time. >> >> So, the pets_reference table has the fields: >> >> refid_pets matching table pets, field id >> refid_personsmatching table persons, field id >> refid_companies matching table companies, field id >> ownersince which is a timestamp >> >> A pet owner can change to persons A, resulting in a record in pets_reference >> connecting pet and person with a timestamp, setting refid_companies to zero >> and refid_persons to person A's record's id value. If the owner changes to >> some other person B, then another record is added to pets_reference. Or if >> the owner for that pet changes to a company, then a new record is added with >> refid_persons being zero and refid_companies being the id value of that >> companies id field value. So at the end of the day pets_reference results in >> a history of owners. >> >> Now, the problem is with displaying a table with pets and only their current >> owners. I can't figure out two things. >> For one it seems I would need to somehow build a query which uses an if-then >> branch to check if companies is zero or persons is zero to ensure to either >> reference a persons or a companies record. >> The second issue is that I only need the max(ownersince) record, because I >> only need the current owner and not past owners. >> >> I toyed around with DISTINCT max(ownersince) and GROUP BY, but it only >> results in errors. I am not the SQL guru, I know my way around so far and am >> learning, but this is kind of another league and I can't really show any >> good results I've come up with so far. Please, can someone help? >> >> Thanks >> Alex >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- 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 no lnger launches
Hi, how do I troubleshoot postgres? It simply won't start any more. I cannot login, not locally nor remotely. I tried a couple of things without success. bash-3.2$ ps -Ac | egrep postgres bash-3.2$ /Library/PostgreSQL/9.1/bin/pg_ctl start -D /Library/PostgreSQL/9.1/data -l postgres.log pg_ctl: another server might be running; trying to start server anyway server starting bash-3.2$ /Library/PostgreSQL/9.1/bin/pg_ctl start -D /Library/PostgreSQL/9.1/data -l postgres.log pg_ctl: another server might be running; trying to start server anyway server starting bash-3.2$ /Library/PostgreSQL/9.1/bin/pg_ctl start -D /Library/PostgreSQL/9.1/data -l postgres.log pg_ctl: another server might be running; trying to start server anyway server starting bash-3.2$ ps -Ac | egrep postgres So sure enough that process claimed to might be starting does not end up being a process. Where do I look for what's actually going on? This is the log output: 04.06.12 00:26:29,792 com.edb.launchd.postgresql-9.1: If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for. 04.06.12 00:26:29,792 com.edb.launchd.postgresql-9.1: The PostgreSQL documentation contains more information about shared memory configuration. 04.06.12 00:26:29,793 com.apple.launchd: (com.edb.launchd.postgresql-9.1[1260]) Exited with code: 1 04.06.12 00:26:29,793 com.apple.launchd: (com.edb.launchd.postgresql-9.1) Throttling respawn: Will start in 10 seconds 04.06.12 00:26:30,000 kernel: nstat_lookup_entry failed: 2 It respawns every ten seconds and fails. Now this installation has been running for a few weeks now without a hiccup. I tried to enable remote login, I might have screwed up the pg_hba.conf file. So I copied the same file from an installation of another server I have on which things are running ok. I restarted but it continues to throw up those respawn messages and shared mem segmets stuff. I changed the sysctl.conf file, but this neither made a difference. This unit has loads of memoryactually the messages seem to be rather bogus. Also, it worked fine, I didn't change the sysctl file before, it seems silly to assume that it just changes itself out of the blue and stops working. I installed nothing, there is no source I can assume to have done so. What can I do to make this server working again? Thanks Alex -- 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 no lnger launches
Btw, this is OSX 10.7. Am 04.06.2012 um 00:39 schrieb Alexander Reichstadt: > Hi, > > how do I troubleshoot postgres? It simply won't start any more. I cannot > login, not locally nor remotely. I tried a couple of things without success. > > bash-3.2$ ps -Ac | egrep postgres > bash-3.2$ /Library/PostgreSQL/9.1/bin/pg_ctl start -D > /Library/PostgreSQL/9.1/data -l postgres.log > pg_ctl: another server might be running; trying to start server anyway > server starting > bash-3.2$ /Library/PostgreSQL/9.1/bin/pg_ctl start -D > /Library/PostgreSQL/9.1/data -l postgres.log > pg_ctl: another server might be running; trying to start server anyway > server starting > bash-3.2$ /Library/PostgreSQL/9.1/bin/pg_ctl start -D > /Library/PostgreSQL/9.1/data -l postgres.log > pg_ctl: another server might be running; trying to start server anyway > server starting > bash-3.2$ ps -Ac | egrep postgres > > So sure enough that process claimed to might be starting does not end up > being a process. Where do I look for what's actually going on? > > This is the log output: > 04.06.12 00:26:29,792 com.edb.launchd.postgresql-9.1: If the request > size is already small, it's possible that it is less than your kernel's > SHMMIN parameter, in which case raising the request size or reconfiguring > SHMMIN is called for. > 04.06.12 00:26:29,792 com.edb.launchd.postgresql-9.1: The PostgreSQL > documentation contains more information about shared memory configuration. > 04.06.12 00:26:29,793 com.apple.launchd: > (com.edb.launchd.postgresql-9.1[1260]) Exited with code: 1 > 04.06.12 00:26:29,793 com.apple.launchd: (com.edb.launchd.postgresql-9.1) > Throttling respawn: Will start in 10 seconds > 04.06.12 00:26:30,000 kernel: nstat_lookup_entry failed: 2 > > > It respawns every ten seconds and fails. Now this installation has been > running for a few weeks now without a hiccup. I tried to enable remote login, > I might have screwed up the pg_hba.conf file. So I copied the same file from > an installation of another server I have on which things are running ok. I > restarted but it continues to throw up those respawn messages and shared mem > segmets stuff. > > I changed the sysctl.conf file, but this neither made a difference. This unit > has loads of memoryactually the messages seem to be rather bogus. Also, > it worked fine, I didn't change the sysctl file before, it seems silly to > assume that it just changes itself out of the blue and stops working. I > installed nothing, there is no source I can assume to have done so. What can > I do to make this server working again? > > Thanks > Alex > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- 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 no lnger launches
Alexander Reichstadt writes: >> So sure enough that process claimed to might be starting does not end up >> being a process. Where do I look for what's actually going on? In the postmaster log ... >> This is the log output: >> 04.06.12 00:26:29,792 com.edb.launchd.postgresql-9.1:If the request >> size is already small, it's possible that it is less than your kernel's >> SHMMIN parameter, in which case raising the request size or reconfiguring >> SHMMIN is called for. You seem to have trimmed off the useful part of this error message, but I'm going to take a wild guess that what is happening is (a) you have another postmaster running someplace, and (b) it is chewing up all the allowed space for shared memory. If you have not mucked with OSX's default shared-memory limits then there is only enough room to run one postmaster at a time. The trouble with this theory is that the other postmaster probably ought to be visible in ps output; I'm not sure why it might not be. Anyway, try running "ipcs" to see if any SysV shared memory or semaphores are active. If there are any, it's a pretty good tip that there's a Postgres running somewhere. 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
[Solved] Re: [GENERAL] [postgres no lnger launches
I found the issue, I changed the connection amount. I changed it back now. Alex Am 04.06.2012 um 00:39 schrieb Alexander Reichstadt: > Hi, > > how do I troubleshoot postgres? It simply won't start any more. I cannot > login, not locally nor remotely. I tried a couple of things without success. > > bash-3.2$ ps -Ac | egrep postgres > bash-3.2$ /Library/PostgreSQL/9.1/bin/pg_ctl start -D > /Library/PostgreSQL/9.1/data -l postgres.log > pg_ctl: another server might be running; trying to start server anyway > server starting > bash-3.2$ /Library/PostgreSQL/9.1/bin/pg_ctl start -D > /Library/PostgreSQL/9.1/data -l postgres.log > pg_ctl: another server might be running; trying to start server anyway > server starting > bash-3.2$ /Library/PostgreSQL/9.1/bin/pg_ctl start -D > /Library/PostgreSQL/9.1/data -l postgres.log > pg_ctl: another server might be running; trying to start server anyway > server starting > bash-3.2$ ps -Ac | egrep postgres > > So sure enough that process claimed to might be starting does not end up > being a process. Where do I look for what's actually going on? > > This is the log output: > 04.06.12 00:26:29,792 com.edb.launchd.postgresql-9.1: If the request > size is already small, it's possible that it is less than your kernel's > SHMMIN parameter, in which case raising the request size or reconfiguring > SHMMIN is called for. > 04.06.12 00:26:29,792 com.edb.launchd.postgresql-9.1: The PostgreSQL > documentation contains more information about shared memory configuration. > 04.06.12 00:26:29,793 com.apple.launchd: > (com.edb.launchd.postgresql-9.1[1260]) Exited with code: 1 > 04.06.12 00:26:29,793 com.apple.launchd: (com.edb.launchd.postgresql-9.1) > Throttling respawn: Will start in 10 seconds > 04.06.12 00:26:30,000 kernel: nstat_lookup_entry failed: 2 > > > It respawns every ten seconds and fails. Now this installation has been > running for a few weeks now without a hiccup. I tried to enable remote login, > I might have screwed up the pg_hba.conf file. So I copied the same file from > an installation of another server I have on which things are running ok. I > restarted but it continues to throw up those respawn messages and shared mem > segmets stuff. > > I changed the sysctl.conf file, but this neither made a difference. This unit > has loads of memoryactually the messages seem to be rather bogus. Also, > it worked fine, I didn't change the sysctl file before, it seems silly to > assume that it just changes itself out of the blue and stops working. I > installed nothing, there is no source I can assume to have done so. What can > I do to make this server working again? > > Thanks > Alex > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- 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 no lnger launches
Thanks, Tom, I messed it up myself, see my other post. Am 04.06.2012 um 01:16 schrieb Tom Lane: > Alexander Reichstadt writes: >>> So sure enough that process claimed to might be starting does not end up >>> being a process. Where do I look for what's actually going on? > > In the postmaster log ... > >>> This is the log output: >>> 04.06.12 00:26:29,792 com.edb.launchd.postgresql-9.1: If the request >>> size is already small, it's possible that it is less than your kernel's >>> SHMMIN parameter, in which case raising the request size or reconfiguring >>> SHMMIN is called for. > > You seem to have trimmed off the useful part of this error message, but > I'm going to take a wild guess that what is happening is (a) you have > another postmaster running someplace, and (b) it is chewing up all the > allowed space for shared memory. If you have not mucked with OSX's > default shared-memory limits then there is only enough room to run one > postmaster at a time. The trouble with this theory is that the other > postmaster probably ought to be visible in ps output; I'm not sure why > it might not be. Anyway, try running "ipcs" to see if any SysV shared > memory or semaphores are active. If there are any, it's a pretty good > tip that there's a Postgres running somewhere. > > 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 -- 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] TYPE TABLE OF NUMBER
On Fri, Jun 1, 2012 at 9:18 PM, utsav wrote: > we are doing mgration activity form oracle to postgresql 9.1 > > How to convert this type : > > ORACLE : > > CREATE OR REPLACE TYPE "NUMBER_ARRAY" IS TABLE OF NUMBER > > please let me know how we can achive this in postgresql > > > My understanding > > POSTGRESQL : > > CREATE TYPE "NUMBER_ARRAY" AS (arr int); > > Appreciate any help ! > > Does this helps ? http://www.postgresql.org/docs/9.1/static/sql-createdomain.html --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Question: How do you manage version control?
> -Ursprungligt meddelande- > Från: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] För Ralf Schuchardt > Skickat: den 1 juni 2012 18:24 > Till: pgsql-general > Ämne: Re: [GENERAL] Question: How do you manage version control? I was using a very simplistic approach a while back. I kept the DDL to create current version from scratch in one file, the DDL to upgrade from last schema version in another and then a small shell script to run the upgrade. I kept the version number in a table for the shell script to check and abort if schema version didn't match and then update after successful upgrade. All files were kept under version control and if I needed older versions of the upgrade file I just browsed the history and got it from there. Very simple, no special tools and easy to run and understand for all developers (even for the "I won't go near sql"-ones). Working with branches makes it a bit harder since the database patch has to be manually handled when merging. Regards, roppert > > Hello, > > am 01.06.2012 um 17:28 schrieb Bryan Montgomery: > > > So we've been reviewing our processes and working on improving them. > One area we've been lacking is a procedure to version control our database > functions, table changes, static data etc. > > we use a very basic system since a few years, consisting mainly of shell > scripts > and sql scripts processed by psql. > > > I'm curious how others do it. Ideally, we want it to be part of our release > > / > build process so that we deploy functions. > > > > However, how do you handle dependancies? > > The code for every recreatable object (i.e. views, functions, maybe types) is > stored in its own file. It includes also a drop statement for these objects > and > formal comments to declare dependencies between the files. The files are > processed with a small script that extracts the dependency declarations and > writes a create and drop script for all objects while maintaining the correct > order. > > > We've also used patch files if we need to modify tables - these are > > typically > written to only run once, ie alter table add column x int. Any thoughts on > putting this in to a process so that it can be run mutliple times without any > issue? > > Our database has a "versions" table, containing the version (and date) of > applied patch files. Every patch file checks the current version in the > database and throws an exception, when it does not match its expected > version. > The directory with the recreatable objects is versioned along the patch files. > > > Regards > Ralf > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
