Re: [SQL] Select row cells as new columns
danycxxx wrote: > Yes, I've looked at it, but id doesn't create the desired output. After more > research I've found that I my design is similar to Entity, Attribute and > Value(EAV) design and I think I have to redesign. Any suggestion regarding > EAV? Is there any other approach? EAV is controversial. I am uncomfortable with it because it implements keys as values. I suggest that you not use EAV. Its putative flexibility comes at a large implementation price. The other approach is relational database design. You model an entity as a collection of tables, each of which represents an aspect of the data pertinent to the entity. Each table has columns, the labels of which correspond generally to the names of attributes for that aspect. Each row of each table provides values for its respective named columns. So a table roughly models what I'll call an "entitylet" - a cohesive piece of the entity model. The rules to decompose entity models into relational data models constitute "normalization". I suggest you create a relational data model normalized to at least third normal form. -- Lew -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Job control in sql
Svenne Krap wrote: > On 29-05-2012 12:32, Ireneusz Pluta wrote: > > W dniu 2012-05-25 10:28, Svenne Krap pisze: > >> Hi. > >> > >> I am building a system, where we have jobs that run at different > >> times (and takes widely different lengths of time). > >> > >> Basically I have a jobs table: > >> > >> create table jobs( > >> id serial, > >> ready boolean, > >> job_begun timestamptz, > >> job_done timestamptz, > >> primary key (id) > >> ); > >> > >> This should run by cron, at it is my intention that the cronjob > >> (basically) consists of > >> / > >> psql -c "select run_jobs()"/ > >> > >> My problem is, that the job should ensure that it is not running > >> already, which would be to set job_begun when the job starts". That > >> can easily happen as jobs should be started every 15 minutes (to > >> lower latency from ready to done) but some jobs can run for hours.. > >> > >> The problem is that a later run of run_jobs() will not see the > >> job_begun has been set by a prior run (that is unfinished - as all > >> queries from the plpgsql-function runs in a single, huge transaction). > >> > >> My intitial idea was to set the isolation level to "read uncommitted" > >> while doing the is-somebody-else-running-lookup, but I cannot change > >> that in the plpgsql function (it complains that the session has to be > >> empty - even when I have run nothing before it). > >> > >> Any ideas on how to solve the issue? > >> > >> I run it on Pgsql 9.1. > >> > >> Svenne > > > > I think you might try in your run_jobs() > > SELECT job_begun FROM jobs WHERE id = job_to_run FOR UPDATE NOWAIT; > > This in case of conflict would throw the exception: > > 55P03 could not obtain lock on row in relation "jobs" > > and you handle it (or not, which might be OK too) in EXCEPTION block. > > > Hehe.. good idea... > > In the mean time I had thought about using advisory locks for the same > thing, but the old-fashioned locks work fine too. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Job control in sql
Sorry about the earlier unfinished post - premature click. Svenne Krap wrote: > Ireneusz Pluta wrote: >> Svenne Krap pisze: > >> I am building a system, where we have jobs that run at different > >> times (and takes widely different lengths of time). > >> > >> Basically I have a jobs table: > >> > >> create table jobs( > >> id serial, > >> ready boolean, > >> job_begun timestamptz, > >> job_done timestamptz, > >> primary key (id) > >> ); > >> > >> This should run by cron, at it is my intention that the cronjob > >> (basically) consists of > >> / > >> psql -c "select run_jobs()"/ > >> > >> My problem is, that the job should ensure that it is not running > >> already, which would be to set job_begun when the job starts". That > >> can easily happen as jobs should be started every 15 minutes (to > >> lower latency from ready to done) but some jobs can run for hours.. > >> > >> The problem is that a later run of run_jobs() will not see the > >> job_begun has been set by a prior run (that is unfinished - as all > >> queries from the plpgsql-function runs in a single, huge transaction). > >> > >> My intitial idea was to set the isolation level to "read uncommitted" > >> while doing the is-somebody-else-running-lookup, but I cannot change > >> that in the plpgsql function (it complains that the session has to be > >> empty - even when I have run nothing before it). > >> > >> Any ideas on how to solve the issue? Use a database to hold data. Use run-time constructs in the program or script to handle run-time considerations. How about using a shell script that uses "ps" to determine if a job is already running, or using a lock file in the file system known to the control script? > >> I run it on Pgsql 9.1. >> >> I think you might try in your run_jobs() >> SELECT job_begun FROM jobs WHERE id = job_to_run FOR UPDATE NOWAIT; >> This in case of conflict would throw the exception: >> 55P03 could not obtain lock on row in relation "jobs" >> and you handle it (or not, which might be OK too) in EXCEPTION block. >> > Hehe.. good idea... > > In the mean time I had thought about using advisory locks for the same > thing, but the old-fashioned locks work fine too. Or don't use the DBMS that way at all. -- Lew -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to use hstore
Hi all I log data in a table using a hstore field. My problem is that i would like to be able to retrieve a group of records from the hstore table and present them in a grid so I would like to convert the hstore records back to the original table format. In the manual i found an example how to convert to a predefined record type, exactly what i like to do but the example works great as long as there is only one record to convert. SELECT * FROM populate_record(null::test, '"col1"=>"456", "col2"=>"zzz"');. But i like to to something like this SELECT * FROM populate_record(null::test, (select logfield from logtable where '"col1"=>"456" )); So i wonder how should i do if i have lots of hstore records to convert ? My SQl knowledge is a bit short :-) Thank you in advance! Jan Eskilsson -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. Please consider the environment before you print this email. (1) The contents of this transmission are privileged and confidential and intended solely for the use of the addressee. Any disclosure, distribution or copying of the contents, other than by the addressee, is strictly prohibited. If you receive this transmission in error, please notify us immediately and destroy the material received. (2) All incoming and outgoing emails and any attachments are subjected to a virus scanner and are believed to be free of any virus, or any other defect which might affect any computer or IT system into which they are received and opened. Therefore, it is the responsibility of the recipient to ensure that they are virus free and no responsibility is accepted by Jan Eskilsson for any loss or damage arising in any way from receipt or use thereof. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to use hstore
On Mon, Jun 4, 2012 at 4:08 PM, Jan Eskilsson wrote: > My problem is that i would like to be able to retrieve a group of > records from the hstore table and present them in a grid so I would > like to convert the hstore records back to the original table format. > In the manual i found an example how to convert to a predefined record > type, exactly what i like to do but the example works great as long as > there is only one record to convert. I think this will answer your question: grayhemp=# \d t Table "public.t" Column | Type | Modifiers +-+--- i | integer | t | text| h | hstore | grayhemp=# select * from t; i | t | h ---+-+ 1 | aaa | 2 | bbb | "a"=>"1", "b"=>"2" 3 | ccc | "a"=>"1", "c"=>"3" (3 rows) grayhemp=# \d r Table "public.r" Column | Type | Modifiers +-+--- a | integer | b | integer | c | integer | grayhemp=# select (populate_record(null::r, h)).* from t; a | b | c ---+---+--- | | 1 | 2 | 1 | | 3 (3 rows) > > SELECT * FROM populate_record(null::test, > '"col1"=>"456", "col2"=>"zzz"');. > > But i like to to something like this > SELECT * FROM populate_record(null::test, > (select logfield from logtable where > '"col1"=>"456" )); > > > So i wonder how should i do if i have lots of hstore records to > convert ? My SQl knowledge is a bit short :-) > > > Thank you in advance! > > > Jan Eskilsson > > -- > No trees were killed in the creation of this message. > However, many electrons were terribly inconvenienced. > > Please consider the environment before you print this email. > > (1) The contents of this transmission are privileged and confidential > and intended solely for the use of the addressee. Any disclosure, > distribution or copying of the contents, other than by the addressee, > is strictly prohibited. If you receive this transmission in error, > please notify us immediately and destroy the material received. > (2) All incoming and outgoing emails and any attachments are subjected > to a virus scanner and are believed to be free of any virus, or any > other defect which might affect any computer or IT system into which > they are received and opened. Therefore, it is the responsibility of > the recipient to ensure that they are virus free and no responsibility > is accepted by Jan Eskilsson for any loss or damage arising in any > way from receipt or use thereof. > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: [email protected] Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to use hstore
Hi Sergey Thats exacly what i was looking for, thanks a million ! Best regards Jan Eskilsson 2012/6/5 Sergey Konoplev : > On Mon, Jun 4, 2012 at 4:08 PM, Jan Eskilsson wrote: >> My problem is that i would like to be able to retrieve a group of >> records from the hstore table and present them in a grid so I would >> like to convert the hstore records back to the original table format. >> In the manual i found an example how to convert to a predefined record >> type, exactly what i like to do but the example works great as long as >> there is only one record to convert. > > I think this will answer your question: > > grayhemp=# \d t > Table "public.t" > Column | Type | Modifiers > +-+--- > i | integer | > t | text | > h | hstore | > > grayhemp=# select * from t; > i | t | h > ---+-+ > 1 | aaa | > 2 | bbb | "a"=>"1", "b"=>"2" > 3 | ccc | "a"=>"1", "c"=>"3" > (3 rows) > > grayhemp=# \d r > Table "public.r" > Column | Type | Modifiers > +-+--- > a | integer | > b | integer | > c | integer | > > grayhemp=# select (populate_record(null::r, h)).* from t; > a | b | c > ---+---+--- > | | > 1 | 2 | > 1 | | 3 > (3 rows) > >> >> SELECT * FROM populate_record(null::test, >> '"col1"=>"456", "col2"=>"zzz"');. >> >> But i like to to something like this >> SELECT * FROM populate_record(null::test, >> (select logfield from logtable where >> '"col1"=>"456" )); >> >> >> So i wonder how should i do if i have lots of hstore records to >> convert ? My SQl knowledge is a bit short :-) >> >> >> Thank you in advance! >> >> >> Jan Eskilsson >> >> -- >> No trees were killed in the creation of this message. >> However, many electrons were terribly inconvenienced. >> >> Please consider the environment before you print this email. >> >> (1) The contents of this transmission are privileged and confidential >> and intended solely for the use of the addressee. Any disclosure, >> distribution or copying of the contents, other than by the addressee, >> is strictly prohibited. If you receive this transmission in error, >> please notify us immediately and destroy the material received. >> (2) All incoming and outgoing emails and any attachments are subjected >> to a virus scanner and are believed to be free of any virus, or any >> other defect which might affect any computer or IT system into which >> they are received and opened. Therefore, it is the responsibility of >> the recipient to ensure that they are virus free and no responsibility >> is accepted by Jan Eskilsson for any loss or damage arising in any >> way from receipt or use thereof. >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > > > -- > Sergey Konoplev > > a database and software architect > http://www.linkedin.com/in/grayhemp > > Jabber: [email protected] Skype: gray-hemp Phone: +79160686204 -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. Please consider the environment before you print this email. (1) The contents of this transmission are privileged and confidential and intended solely for the use of the addressee. Any disclosure, distribution or copying of the contents, other than by the addressee, is strictly prohibited. If you receive this transmission in error, please notify us immediately and destroy the material received. (2) All incoming and outgoing emails and any attachments are subjected to a virus scanner and are believed to be free of any virus, or any other defect which might affect any computer or IT system into which they are received and opened. Therefore, it is the responsibility of the recipient to ensure that they are virus free and no responsibility is accepted by Jan Eskilsson for any loss or damage arising in any way from receipt or use thereof. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
