Re: [SQL] tree structure photo gallery date quiery
Gary, if you need really fast solution for you task and dont't afraid non-standard soltion, take a look on contrib/ltree module. http://www.sai.msu.su/~megera/postgres/gist/ltree/ Oleg On Wed, 17 Nov 2004, Gary Stainburn wrote: On Tuesday 16 November 2004 1:08 pm, sad wrote: On Tuesday 16 November 2004 14:29, Gary Stainburn wrote: Hi folks. I'm looking at the possibility of implementing a photo gallery for my web site with a tree structure How would I go about creating a view to show a) the number of photos in a gallery and b) the timestamp of the most recent addition for a gallery, so that it interrogates all sub-galleries? nested-tree helps you associate a numeric interval [l,r] with each record of a tree and let father interval include all its children intervals and brother intervals never intersect see the article http://sf.net/projects/redundantdb for detailed examples and templates Hi Sad, I had actually started working on this because I found an old list posting archived on the net at http://www.net-one.de/~ks/WOoK/recursive-select. As you can see below, I've got the tree structure working and can select both a node's superiors and it's subordinates. Using these I can also find a node's last added date and photo count. However, I've got two problems. Firstly, below I've got the two example selects for listing owners and owned nodes. I can't work out how to convert these two parameterised selects into views. Secondly, in order to get the results shown here, I've had to write two seperate but similar pl/pgsql functions to return the photo_count and photo_updated columns, which result in 2 * select per call * twice per line * 7 lines = 28 selects Is there a more efficient way? nymr=# select *, photo_count(id), photo_updated(id) from gallery; id | parent |name| photo_count | photo_updated +++-+ 1 | 0 | Root | 4 | 2004-11-10 12:12:00+00 2 | 1 | NYMR | 3 | 2004-11-10 12:12:00+00 3 | 1 | Middleton | 1 | 2004-01-01 09:12:12+00 4 | 2 | Steam Gala | 2 | 2004-11-10 12:12:00+00 5 | 2 | Diesel Gala| 1 | 2004-10-01 10:00:00+01 6 | 2 | From The Footplate | 0 | 7 | 3 | From The Footplate | 1 | 2004-01-01 09:12:12+00 (7 rows) Below is everything I have so far, including one of the functions I'm using: create table gallery ( id serial, parent int4, namevarchar(40), primary key (id)); create table photos ( pid serial, id int4 references gallery not null, added timestamp, pfile varchar(128) not null, pdesc varchar(40) not null, primary key (pid)); create table tree ( -- seperate for now to ease development id int4 references gallery not null, lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1), CONSTRAINT order_okay CHECK (lft < rgt) ); copy "gallery" from stdin; 1 0 Root 2 1 NYMR 3 1 Middleton 4 2 Steam Gala 5 2 Diesel Gala 6 2 From The Footplate 7 3 From The Footplate \. copy "photos" from stdin; 1 4 2004-11-10 10:10:00 80135-1.jpg 80135 light-engine 2 4 2004-11-10 12:12:00 6619-1.jpg 6619 on-shed 3 5 2004-10-01 10:00:00 D7628.jpg Sybilla 4 7 2004-01-01 09:12:12 mm-21.jpg No. 4 Mathew Murrey \. copy "tree" from stdin; 1 1 14 2 2 9 3 10 13 4 3 4 5 5 6 6 7 8 7 11 12 \. -- select leaf and parents -- want to convert to a view so I can type something like -- 'select * from root_path where id = 7; nymr=# select p2.id, g.parent, g.name from gallery g, tree p1, tree p2 where g.id = p2.id and p1.lft between p2.lft and p2.rgt and p1.id = 7; id | parent |name ++ 1 | 0 | Root 3 | 1 | Middleton 7 | 3 | From The Footplate (3 rows) -- Select parent and subordinates - also want to convert to view nymr=# select p1.*, g.* from tree as p1, tree as p2, gallery g where g.id = p1.id and p1.lft between p2.lft and p2.rgt and p2.id = 1; id | lft | rgt | id | parent |name +-+-+++ 1 | 1 | 14 | 1 | 0 | Root 2 | 2 | 9 | 2 | 1 | NYMR 3 | 10 | 13 | 3 | 1 | Middleton 4 | 3 | 4 | 4 | 2 | Steam Gala 5 | 5 | 6 | 5 | 2 | Diesel Gala 6 | 7 | 8 | 6 | 2 | From The Footplate 7 | 11 | 12 | 7 | 3 | From The Footplate (7 rows) -- use the one above to select photos - another view nymr=# select count(pid), max(added) from photos where id in ( nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft and p2.rgt and p2.id = 1 nymr(# ); count | max ---+ 4
Re: [SQL] tree structure photo gallery date quiery
O Oleg Bartunov έγραψε στις Nov 17, 2004 :
> Gary,
>
> if you need really fast solution for you task and dont't afraid
> non-standard soltion, take a look on contrib/ltree module.
> http://www.sai.msu.su/~megera/postgres/gist/ltree/
>
> Oleg
Oleg how would you compare an ltree solution against a
genealogical approach using intarray??
i.e. store for each node, its path to root,
e.g. path='{4,2,7}'::int4[] where 4 is the id of the father,
2 of the grandfather, and 7 of the root, whereas root has
path is null.
That way we can do really fast queries using ~, have indexes
on level (path length),first (immediate ancestor),last (root)
using C functions that we can easily write and so forth.
I have extensively used this approach with success.
Can you comment on the pros and cons of each? (int[] with intarray vs
ltree).
Thanx
> _
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
-Achilleus
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] tree structure photo gallery date quiery
On Wed, 17 Nov 2004, Achilleus Mantzios wrote:
O Oleg Bartunov ?? Nov 17, 2004 :
Gary,
if you need really fast solution for you task and dont't afraid
non-standard soltion, take a look on contrib/ltree module.
http://www.sai.msu.su/~megera/postgres/gist/ltree/
Oleg
Oleg how would you compare an ltree solution against a
genealogical approach using intarray??
i.e. store for each node, its path to root,
e.g. path='{4,2,7}'::int4[] where 4 is the id of the father,
2 of the grandfather, and 7 of the root, whereas root has
path is null.
That way we can do really fast queries using ~, have indexes
on level (path length),first (immediate ancestor),last (root)
using C functions that we can easily write and so forth.
I have extensively used this approach with success.
Can you comment on the pros and cons of each? (int[] with intarray vs
ltree).
well, internally both approaches are very much the same :)
if you don't need ltree features like human readable path
But I'd like to have some real numbers. Could you spent some time and
create test suite and compare genealogical approach and ltree ?
I'll add resuts to docs.
Thanx
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[SQL] session_id
hi all, is there a way to determine the session id on a database session? I would need to have a unique number whenever a session is started, and have this available as a function or view result. thanks. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] session_id
Riccardo G. Facchini wrote:
hi all,
is there a way to determine the session id on a database session?
I would need to have a unique number whenever a session is started, and
have this available as a function or view result.
Add a new sequence to your database:
CREATE SEQUENCE my_session_id;
Then, at the start of every session:
SELECT nextval('my_session_id');
and whenever you need the value:
SELECT currval('my_session_id');
Sequences are concurrency-safe, so you're OK with multiple clients. They
return INT8 values, so you should be good for unique numbers for a while.
The only thing is, you need to remember to call nextval() every time you
connect.
HTH
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] session_id
O Richard Huxton έγραψε στις Nov 17, 2004 :
> Riccardo G. Facchini wrote:
> > hi all,
> >
> > is there a way to determine the session id on a database session?
> >
> > I would need to have a unique number whenever a session is started, and
> > have this available as a function or view result.
Why not SELECT pg_backend_pid();
??
>
> Add a new sequence to your database:
>CREATE SEQUENCE my_session_id;
>
> Then, at the start of every session:
>SELECT nextval('my_session_id');
>
> and whenever you need the value:
>SELECT currval('my_session_id');
>
> Sequences are concurrency-safe, so you're OK with multiple clients. They
> return INT8 values, so you should be good for unique numbers for a while.
>
> The only thing is, you need to remember to call nextval() every time you
> connect.
>
> HTH
>
--
-Achilleus
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] session_id
--- Richard Huxton <__> wrote:
> Riccardo G. Facchini wrote:
> > hi all,
> >
> > is there a way to determine the session id on a database session?
> >
> > I would need to have a unique number whenever a session is started,
> and
> > have this available as a function or view result.
>
> Add a new sequence to your database:
>CREATE SEQUENCE my_session_id;
>
> Then, at the start of every session:
>SELECT nextval('my_session_id');
>
> and whenever you need the value:
>SELECT currval('my_session_id');
>
> Sequences are concurrency-safe, so you're OK with multiple clients.
> They
> return INT8 values, so you should be good for unique numbers for a
> while.
>
> The only thing is, you need to remember to call nextval() every time
> you
> connect.
>
> HTH
> --
>Richard Huxton
>Archonet Ltd
>
Good idea, but it won't work for what I need.
I'll be able to do get the nextval('my_session_id') as soon as the
session initiates, but my problem is that I need to make all the
subsecuent actions aware of that particular value. using
currval('my_session_id') is not good, as any other session is likely to
also change my_session_id to another value.
I was looking more on the pg_stat_activity view, but the problem I face
is that I'm not sure on how to retrieve the unique
pg_stat_get_backend_pid that corresponds to my own job...
thank you,
any other suggestion?
regards,
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] session_id
--- Achilleus Mantzios <__> wrote: > O Richard Huxton Ýãñáøå óôéò Nov 17, 2004 : > > > Riccardo G. Facchini wrote: > > > hi all, > > > > > > is there a way to determine the session id on a database session? > > > > > > I would need to have a unique number whenever a session is > started, and > > > have this available as a function or view result. > > Why not SELECT pg_backend_pid(); > ?? > > > [..] that could work. Is this the unique value for my own session? can somebody confirm it? regards, R. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] session_id
Achilleus Mantzios wrote: O Richard Huxton έγραψε στις Nov 17, 2004 : Riccardo G. Facchini wrote: hi all, is there a way to determine the session id on a database session? I would need to have a unique number whenever a session is started, and have this available as a function or view result. Why not SELECT pg_backend_pid(); ?? This is guaranteed to be unique while connected, but if you want to track sessions over time isn't guaranteed to be. So, if you might have pid=1234 now and also a month ago in a different session (especially if you had a server reboot in-between). Of course, if Riccardo doesn't need that, the pid is fine. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Counting Distinct Records
On Tue, 16 Nov 2004, Thomas F.O'Connell wrote: > Hmm. I was more interested in using COUNT( * ) than DISTINCT *. > > I want a count of all rows, but I want to be able to specify which > columns are distinct. I'm now a bit confused about exactly what you're looking for in the end. Can you give a short example? > That's definitely an interesting approach, but testing doesn't show it > to be appreciably faster. > > If I do a DISTINCT *, postgres will attempt to guarantee that there are > no duplicate values across all columns rather than a subset of columns? > Is that right? It guarantees one output row for each distinct set of column values across all columns. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] session_id
--- Richard Huxton <__> wrote: > Achilleus Mantzios wrote: > > O Richard Huxton Ýãñáøå óôéò Nov 17, 2004 : > > > > > >>Riccardo G. Facchini wrote: > >> > >>>hi all, > >>> > >>>is there a way to determine the session id on a database session? > >>> > >>>I would need to have a unique number whenever a session is > started, and > >>>have this available as a function or view result. > > > > > > Why not SELECT pg_backend_pid(); > > ?? > > This is guaranteed to be unique while connected, but if you want to > track sessions over time isn't guaranteed to be. So, if you might > have > pid=1234 now and also a month ago in a different session (especially > if > you had a server reboot in-between). > > Of course, if Riccardo doesn't need that, the pid is fine. > > -- >Richard Huxton >Archonet Ltd > Ok. Taken note. No. I don't need that, as I only need it during the session itself. Once ended, the session info is not required over time. thanks to all for your suggestions, Riccardo ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] session_id
Riccardo G. Facchini wrote:
Add a new sequence to your database:
CREATE SEQUENCE my_session_id;
Then, at the start of every session:
SELECT nextval('my_session_id');
and whenever you need the value:
SELECT currval('my_session_id');
Good idea, but it won't work for what I need.
I'll be able to do get the nextval('my_session_id') as soon as the
session initiates, but my problem is that I need to make all the
subsecuent actions aware of that particular value. using
currval('my_session_id') is not good, as any other session is likely to
also change my_session_id to another value.
No - other sessions will see different values. Test it and see.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] session_id
On Wed, Nov 17, 2004 at 06:25:25AM -0800, Riccardo G. Facchini wrote:
>
> --- Richard Huxton <__> wrote:
> >
> > Add a new sequence to your database:
[snip]
> Good idea, but it won't work for what I need.
> I'll be able to do get the nextval('my_session_id') as soon as the
> session initiates, but my problem is that I need to make all the
> subsecuent actions aware of that particular value. using
> currval('my_session_id') is not good, as any other session is likely to
> also change my_session_id to another value.
Where did you get the idea that currval() would be affected by other
sessions? Richard mentioned that sequences are concurrency-safe,
as does the documentation.
http://www.postgresql.org/docs/7.4/static/functions-sequence.html
Can you give us an example of where this wouldn't work?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Counting Distinct Records
The specific problem I'm trying to solve involves a user table with some history. Something like this: create table user_history ( user_id int event_time_stamp timestamp ); I'd like to be able to count the distinct user_ids in this table, even if it were joined to other tables. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Nov 17, 2004, at 8:52 AM, Stephan Szabo wrote: On Tue, 16 Nov 2004, Thomas F.O'Connell wrote: Hmm. I was more interested in using COUNT( * ) than DISTINCT *. I want a count of all rows, but I want to be able to specify which columns are distinct. I'm now a bit confused about exactly what you're looking for in the end. Can you give a short example? That's definitely an interesting approach, but testing doesn't show it to be appreciably faster. If I do a DISTINCT *, postgres will attempt to guarantee that there are no duplicate values across all columns rather than a subset of columns? Is that right? It guarantees one output row for each distinct set of column values across all columns. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
