Thread: Distributed Transactions

Jinujose,

> i am using PostgreSQL 7.3.2. How can i do distributed transactions in 
PostgreSQL.
> is there a transaction coordinator available for Postgres..

Distributed transactions?   Transaction coodinator?   I'm not quite sure what 
these are.

If you mean Two Phase Commit, for committing a transaction across multiple 
servers/databases, it's under development and may be released with version 
7.5.   Or later.

-- 
-Josh BerkusAglio Database SolutionsSan Francisco

On Wed, 18 Feb 2004, George A.J wrote:

> Hi all,
>  
> i am using PostgreSQL 7.3.2. How can i do distributed transactions in PostgreSQL.
> is there a transaction coordinator available for Postgres..

there isn't one, really, but you could likely roll something that worked.  

Oh, and update your version of postgresql.  There's a nasty "won't 
startup" bug in 7.3.2 you'd just as soon rather avoid.  It doesn't lose 
data, but there's nothing like restarting your database only to have it 
complain about some boundary condition in the write ahead log and then 
have to spend an hour or so with it down while you download the updates to 
get it working.  7.3.5 is the latest version of the 7.3 branch, but it 
looks like rpm wise, the latest on the sites is 7.3.4.

Hi,

I have a query that reads:

SELECT DISTINCT ON (messageboard.threadid) messageboard.threadid,
messageboard.topic, owner.ownerid, owner.username FROM messageboard, owner
WHERE messageboard.ownerid=owner.ownerid AND messageboard.leagueid =
'$leagueid' ORDER BY messageboard.messageid DESC LIMIT $entries_on_page
OFFSET $beginThread"

The purpose of this query is to retrieve unique threadid's in the order of
the most recent posts that have been made to each thread.  When I use this
query I get an error that:

"SELECT DISTINCT ON expressions must match initial ORDER BY expressions".

Of course, if I put ORDER BY threadid in the query it would order it in the
order that the thread was created, not in the last post made.

I have tried using GROUP BY threadID, I get a similar order.

Am I just approaching this all wrong and need to create a temporary table
and draw from that, or is there a way to salvage this query?

Thanks so much,
Jeremy

Jeremy,

> Am I just approaching this all wrong and need to create a temporary table
> and draw from that, or is there a way to salvage this query?

Think about using a subquery instead of the DISTINCT ON approach.   I don't 
think you can get what you want with DISTINCT ON.

A temporary table is not necessary.


-- 
-Josh BerkusAglio Database SolutionsSan Francisco

Thanks Josh,

I'll do that, I just wasn't sure if I was missing something obvious.

Jeremy

-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Thursday, February 19, 2004 2:29 PM
To: jer@highboard.com; pgsql-sql@postgresql.org
Subject: Re: [SQL] DISTINCT ON troubles


Jeremy,

> Am I just approaching this all wrong and need to create a temporary table
> and draw from that, or is there a way to salvage this query?

Think about using a subquery instead of the DISTINCT ON approach.   I don't
think you can get what you want with DISTINCT ON.

A temporary table is not necessary.


--
-Josh BerkusAglio Database SolutionsSan Francisco

( sorry if this is a repeat, my mail server is being wonky today )

I'm looking for a way, within SQL, given a starting date and an ending 
date, to get back the number of months between the start and end date. 
If I "SELECT end_date - start_date", I get back an interval in days; I 
need months.

Thanks for any suggestions,
Brian

On Thu, 19 Feb 2004, Brian Knox wrote:

> ( sorry if this is a repeat, my mail server is being wonky today )
> 
> I'm looking for a way, within SQL, given a starting date and an ending 
> date, to get back the number of months between the start and end date. 
> If I "SELECT end_date - start_date", I get back an interval in days; I 
> need months.

Maybe date_part?

select date_part('month','2004-08-02 12:00:00'::timestamp) - 
date_part('month','2004-05-01 12:00:00'::timestamp);

On Thu, 19 Feb 2004, scott.marlowe wrote:

> On Thu, 19 Feb 2004, Brian Knox wrote:
> 
> > ( sorry if this is a repeat, my mail server is being wonky today )
> > 
> > I'm looking for a way, within SQL, given a starting date and an ending 
> > date, to get back the number of months between the start and end date. 
> > If I "SELECT end_date - start_date", I get back an interval in days; I 
> > need months.
> 
> Maybe date_part?
> 
> select date_part('month','2004-08-02 12:00:00'::timestamp) - 
> date_part('month','2004-05-01 12:00:00'::timestamp);

Note that I think you need a +1 at the end of that...

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> Oh, and update your version of postgresql.  There's a nasty "won't 
> startup" bug in 7.3.2 you'd just as soon rather avoid.

Actually I think the "won't start" bug was in 7.3.3 :-(.  Still, it's
good advice to keep up with the latest version in your release series.
We don't make such releases just to amuse ourselves.
        regards, tom lane

Sorry, Not looking for a way to extract a month from a timestamp. I'm 
looking for a way to convert an interval from days to months. I'm not 
sure after digging into it that there is a way to handle it in SQL, as 
the interval that results from subtracting one timestamp from another is 
not away of what months the interval spans, so there'd be no proper way 
to take month lengths into account.

I gave up on the sql and used Date::Calc from Perl. Thanks for your 
answer anyway.

scott.marlowe wrote:
> On Thu, 19 Feb 2004, Brian Knox wrote:
> 
> 
>>( sorry if this is a repeat, my mail server is being wonky today )
>>
>>I'm looking for a way, within SQL, given a starting date and an ending 
>>date, to get back the number of months between the start and end date. 
>>If I "SELECT end_date - start_date", I get back an interval in days; I 
>>need months.
> 
> 
> Maybe date_part?
> 
> select date_part('month','2004-08-02 12:00:00'::timestamp) - 
> date_part('month','2004-05-01 12:00:00'::timestamp);
> 

I'm still not sure what you were looking for.

If you have, say, March 16th, and the next date is August 23rd, do you 
want to count March, April, May, Jun, July, August = 6?  Or do you want to 
count the number of 30 day periods?

Using date_part gets you the first one.

Plus, since months can be 28, 29, 30, or 31 days long, how exactly does 
one convert 58 days to months?  Without knowing the month the start and 
end dates have, you wouldn't know for sure if it was two or three.  and 
what about parts of months, a week in the end of march, all of april, and 
the first two weeks of May, is that two months or three?

I guess my point is that I'm not sure what question you were asking, so 
I'm not sure how Perl's Date::Calc did better or worse than would 
postgresql.

On Fri, 20 Feb 2004, Brian Knox wrote:

> Sorry, Not looking for a way to extract a month from a timestamp. I'm 
> looking for a way to convert an interval from days to months. I'm not 
> sure after digging into it that there is a way to handle it in SQL, as 
> the interval that results from subtracting one timestamp from another is 
> not away of what months the interval spans, so there'd be no proper way 
> to take month lengths into account.
> 
> I gave up on the sql and used Date::Calc from Perl. Thanks for your 
> answer anyway.
> 
> scott.marlowe wrote:
> > On Thu, 19 Feb 2004, Brian Knox wrote:
> > 
> > 
> >>( sorry if this is a repeat, my mail server is being wonky today )
> >>
> >>I'm looking for a way, within SQL, given a starting date and an ending 
> >>date, to get back the number of months between the start and end date. 
> >>If I "SELECT end_date - start_date", I get back an interval in days; I 
> >>need months.
> > 
> > 
> > Maybe date_part?
> > 
> > select date_part('month','2004-08-02 12:00:00'::timestamp) - 
> > date_part('month','2004-05-01 12:00:00'::timestamp);
> > 
> 

I'm looking for a way, within SQL, given a starting date and an ending 
date, to get back the number of months between the start and end date. 
If I "SELECT end_date - start_date", I get back an interval in days; I 
need months.

Thanks for any suggestions,
Brian