date_trunc( 'week', now() - interval '1 week' )
AND
date_trunc( 'week', now() - interval '1 week' )
+ interval '1 week' - interval '1 second'
Is there a better approach?
--
Bill Moseley
mose...@hank.org
Sent from my i
. Oddly, it didn't and the deadlock was reported
then on the LOCK itself, plus my deadlock_timeout (60 seconds) didn't seem to
apply in that case. A mystery.
Thanks for the help,
--
Bill Moseley.
mose...@hank.org
Sent from my iMutt
--
Sent via pgsql-general mailing list (pgs
lock to use and on
what? SHARE ROW EXCLUSIVE on the account table before issuing the update?
--
Bill Moseley.
mose...@hank.org
Sent from my iMutt
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sat, May 02, 2009 at 11:48:21AM -0400, Tom Lane wrote:
> Bill Moseley writes:
> > Not getting any nibbles, so allow me to try a short question:
> > If I have a deadlock situation (that will be reported as such by
> > Postgresql once the deadlock_timeout passes), does pg_s
I'm seeing below.
On Thu, Apr 30, 2009 at 10:30:26AM -0700, Bill Moseley wrote:
>
> I need a bit of help understanding what might be causing a deadlock.
>
> To duplicate the problem I'm running a test script that forks two
> child processes. Each child runs the same
reign keys) and I'm still getting a deadlock.
In general, do the constraints need to be deferrable and then defer
constraints at the start of the transaction?
What else can I do to debug?
Thanks,
--
Bill Moseley
mose...@hank.org
Sent from my iMutt
--
Sent via pgsql-general mailing list (
's secret word.
Then if they also hacked the credit card server they could then
decrypt the data using passwords they were able to sniff.
See any glaring holes?
Thanks for the help!
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ny comments or recommendations from others that have
implemented something similar.
Thanks,
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail comman
to make sure that mailbox.mailbox_group references a
group that has a matching owner?
Or do I remove the "owner" column from mailbox table and alter all my
access to mailbox to now do a join with the mailbox_group table (to
find the owner)?
(Or do I wonder why
aware of how the actual table
data was organized and fetched into memory.
> "Premature optimization is the root of all evil"
Exactly what prompted this thread. ;)
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
> If you alter tables "customer" and "order", taking some columns off,
> and stowing them in separate tables, then you'll find that more tuples
> of "customer" and "order" will fit into a buffer page, and that the
> join will
On Tue, May 15, 2007 at 07:51:44AM +0200, Dawid Kuroczko wrote:
> On 5/15/07, Bill Moseley <[EMAIL PROTECTED]> wrote:
> >On Tue, May 15, 2007 at 06:33:26AM +0200, Dawid Kuroczko wrote:
> >> Well, views are not going to help with memory consumption here.
> >> I
Can anyone provide input on this question? I'm curious how to look at
this from a disk and memory usage perspective. Would using a bit
column type help much?
I'm not thrilled by the loss of referential integrity.
On Mon, May 14, 2007 at 01:37:18PM -0700, Bill Moseley wrote:
>
&
passowrd, email,
[10 other columns]
from user_table;
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column&
he forum, for instance, only a small
> part of the fields in the Users table was needed, the rest was just
> dead
> weight, that made the table unable to fit in RAM.
Well, that's part of my question. If not selecting those columns in
the common selects h
f there might be overriding
concerns at times.
For example, if I have 1 million users and they each like all colors
and thus have a 20 million row link table how much space would be
saved by using a bit column as above?
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
On Fri, Mar 09, 2007 at 01:27:51PM -0800, [EMAIL PROTECTED] wrote:
> You can/should create it as an AFTER UPDATE trigger. The OLD row will
> contain the previous values.
Curiously, also works with a BEFORE UPDATE.
Off to review the docs
--
Bill Moseley
[EMAIL PRO
On Fri, Mar 09, 2007 at 06:50:39PM -0500, Tom Lane wrote:
> Bill Moseley <[EMAIL PROTECTED]> writes:
> > I'm asking for a sanity check:
>
> > And then an audit table:
>
> > create table template_history (
> > id
cern if
another trigger was to modify the row. But, I can't seem to find that
post now which is why I'm asking for the sanity check.
Are there potential problems with this setup?
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)
file as you suggest would make finding all
"files" at a given directory level simple, too.
But, I'm not thrilled about the possibility of the hard-coded path not
matching the path up the tree to the root node, though. Which, of
course, is why I posted. But, I'll give it
I
> use it and like it a lot.
Yes, I have seen it. I just thought it seemed like a very large
"hammer" to use form my task -- quite a few more query methods than I
need . But, perhaps I should look at it again and get a better
understanding of what it can do.
Thank
en I wonder which is a better approach.
I assume this is a reasonably common problem so I'm curious how others
have implemented it.
Thanks,
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the pla
On Thu, Jun 22, 2006 at 08:16:05AM -0400, Douglas McNaught wrote:
> Bill Moseley <[EMAIL PROTECTED]> writes:
>
> > Hum. lsof knows about the file.
> >
> > $ lsof -p 1512 | grep /var/run
> > postmaste 1512 postgres4u unix 0xf78b5980 16
pg_ident.conf
Hum. lsof knows about the file.
$ lsof -p 1512 | grep /var/run
postmaste 1512 postgres4u unix 0xf78b5980 1631
/var/run/postgresql/.s.PGSQL.5432
Any ideas what happened to the socket?
I had to stop and start the postmaster to get the socket back.
--
Bill
ould be necessary for the same reasons above -
> however if you do need to build something, grab
> postgresql-server-dev-8.1 :)
Great. Thanks for all the hand holding. ;)
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1:
but how do you use psql with both versions?
And I assume I'll need to rebuild DBD::Pg -- and any tricks getting
it to link with the correct client library?
It's the "managed" part of the Dreamhost server that has me
worried. ;)
Thanks,
--
Bill Moseley
[EMAIL PROTECTE
anyone that has been through this have any advice? There isn't
an init.d script in the distribution, right?
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
table in the trigger
and check the current status of the table and set the column based on
that current status.
I want to make sure that between the time the trigger completes and
when the insert finally happens that another session can't also do an
insert and see the same ta
ore than one table
insert into semething (default);
and:
begin;
-- fires a trigger that updates more than one table
insert into somthing (default);
commit;
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TI
warning. When would issuing a
lock table outside an explicit transaction be of any use?
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command
't have updates in other
sessions due to the lock so I'll see a frozen view of that table
regardless of serializable.
In other words, using serializable doesn't add anything if the table
is already locked in the transaction and all I'm looking at is that
one locked table.
Thanks,
aracter encodings.
Is the Holy Grail encoding and lc_collate settings per column?
Changing topics, but I'm going to play with different cluster
settings for collate. If I create a cluster in given directory
is there any problems with moving that cluster (renaming the
directory)?
Th
On Sat, Feb 18, 2006 at 01:40:09PM -0500, Tom Lane wrote:
> Bill Moseley <[EMAIL PROTECTED]> writes:
> > - To clarify the first point, if the database is encoded utf-8 and
> > lc_collate is en_US then Postgresql does NOT try to convert utf-8 to
> > 8859-1 before sorting
n that point.
> > 5) I suppose there's not way to answer this, short of running
> > benchmarks, but any ideas what using a lc_collate with utf-8 would do
> > to performance? Is it a big hit?
>
> I don't know why that would be a problem.
Just that sorting utf8 i
ee:
Zo
But, if on that same remote machine I run a unicode xterm (uxterm in
Debian) then in that xterm window I do:
utf8test=> \encoding
UNICODE
utf8test=> select first_name from person where last_name = 'Anderson';
Zoë (correct)
On Sat, Feb 11, 2006 at 12:35:34AM -0700, Michael Fuhr wrote:
> On Fri, Feb 10, 2006 at 11:59:30AM -0800, Bill Moseley wrote:
> > How do I make a join on the class table but not effect the left outer
> > join?
>
> Are you looking for something like this?
>
> LEFT O
review_mode IS FALSE
AND class_domain.class = c.id
AND class_domain.domain = 1
)
But, that's not part of the join, of course.
How do I make a join on the class table but not effect the left outer
join?
Thanks,
--
Bill Moseley
[EMAIL PR
On Mon, Nov 21, 2005 at 02:06:35PM -0800, Bill Moseley wrote:
> The now working query (thanks to you!) is:
No that doesn't work. It's dropping the people that have never
been assigned a class to teach (i.e. don't have a row in the
"instructors" link table).
>
0 |4
31 | 4 | 4 | 0 |4
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: explain analyze is your friend
On Mon, Nov 21, 2005 at 11:45:34AM -0600, Bruno Wolff III wrote:
> On Mon, Nov 21, 2005 at 05:40:10 -0800,
> Bill Moseley <[EMAIL PROTECTED]> wrote:
> >
> > Here's where I'm missing something. Trying to do an outer join on
> > to bring in the class
class = class.id ),
person_role
WHERE person_role.person = person.id
AND person_role.role = 3
GROUP BY person.id, last_name;
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: if posting/reading throug
m (eg, you could fix it to display the lowest-numbered instructor
> id for the particular class). Even then, are you searching for the
> instructor id that the view happens to show for that class, or some
> other one?
Well, clearly, my "one-size-fits-all view" doesn't w
On Wed, Nov 16, 2005 at 07:48:06PM -0500, Tom Lane wrote:
> Bill Moseley <[EMAIL PROTECTED]> writes:
> > [ strange behavior ]
>
> Oh, duh, it's not a PG bug: the problem is that the view is
> underspecified. You have
>
> SELECT DISTINCT ON (class.id)
>
On Wed, Nov 16, 2005 at 10:53:21AM -0500, Tom Lane wrote:
> Bill Moseley <[EMAIL PROTECTED]> writes:
> > The first plan below returns the correct number of rows, the second plan
> > does
> > not. These are after I did the reindex, btw.
>
> Bizarre. What are t
; Index Scan using
instructors_class_index on instructors (cost=0.00..52.00 rows=1000 width=8)
-> Hash (cost=20.00..20.00 rows=1000
width=142)
-> Seq Scan on "location"
(cost=0.00..20.00 rows=1000 width=
PostgreSQL 7.4.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.2 20050816
(prerelease) (Debian 4.0.1-5)
Hopefully this is something simple -- I assume it's a problem with my
SQL. But it looks really weird to me at this late hour.
I have some tables for managing workshops, and I have a view
integer | not null
Indexes:
"person_role_pkey" primary key, btree (person, role)
Foreign-key constraints:
"$2" FOREIGN KEY (role) REFERENCES role(id) ON DELETE RESTRICT
"$1" FOREIGN KEY (person) REFERENCES person(id) ON DELETE CASCADE
Thanks,
--
Bil
e-normalize the data.
Maybe I can just create three views (future, recent, old) and live
with that.
The temporary table is another possibility I'll look into.
Thank you very much for spending time on this. I really appreciate
it.
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
SELECT *
FROM
(SELECT person.id AS person_id, first_name
FROM person) p
INNER JOIN
(SELECT class.id AS class_id, class_time
FROM class) c
INNER JOIN
(SELECT person, count(class) AS class_count
UP BY
clause or be used in an aggregate function
Is there a reason Postgresql doesn't just add the column
automatically? It does in other cases (like a missing table in a
join).
Thanks
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
On Fri, Sep 16, 2005 at 10:02:28AM -0500, Thomas O'Connell wrote:
>
> On Sep 16, 2005, at 9:18 AM, Bill Moseley wrote:
>
> >First question is why the planner is not using an index scan when I
> >use "now()" or CURRENT_TIMESTAMP?
>
> It also used to be
ACUUM (or
> both) or tweaking the statistics parameters of the columns.
I did wonder about the planner stats, so I had run ANALYZE on the
database with no change. I just now ran VACUUM (and VACUUM ANALYZE),
and again see no change. Perhaps my table is just too small for this
test.
--
Bill Mo
81..707.90 rows=437 width=8)
Sort Key: instructors.person
-> Hash IN Join (cost=656.65..687.64 rows=437 width=8)
Hash Cond: ("outer"."class" = "inner".id)
-> Seq Scan on instructors (cost=0
rouped columns except within aggregate
functions, since there would be more than one possible value to
return for an ungrouped column.
Frankly, I cannot see how it might change results of a select between
the two views. Am I missing something?
--
Bill Mosele
't really
care), but class_time is a column in the "class" table which I'm using
DISTINCT ON on, so that should be unique as well. So I assume you
meant random choice of first_name, not class_time.
Thanks,
--
Bill Moseley
[EMAIL PROTECTED]
---(end o
And about being efficient:
On Thu, Aug 25, 2005 at 08:01:26AM -0700, Bill Moseley wrote:
> DROP VIEW cl;
> CREATE VIEW cl (id, class_time, instructor)
> AS
> SELECT DISTINCT ON(class.id)
>class.id, class.class_time, pe
On Thu, Aug 25, 2005 at 08:05:36AM -0500, Bruno Wolff III wrote:
> On Wed, Aug 24, 2005 at 23:12:17 -0700,
> Bill Moseley <[EMAIL PROTECTED]> wrote:
> > I need a little SQL help:
> >
> > I'm trying to get a subselect working inside a view.
>
> Unfort
ass_time | instructor
-++
544 | 2005-08-31 09:00:00-07 | Cheryl
555 | 2005-09-30 09:00:00-07 | Cheryl
737 | 2005-08-30 09:00:00-07 | Cynthia
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
k. I suspect I'm missing
something obvious.
Thanks,
Oh BTW -- If I do a count(*) and a WHERE that only includes columns in
the "class" table on the VIEW, will Postgresql still do the joins? Or
will it only do the select on the "class" table. I
ExecNestLoop ()
#4 0x08102ceb in ExecProcNode ()
#5 0x081093a4 in ExecAgg ()
#6 0x08102c79 in ExecProcNode ()
#7 0x08101ecc in ExecutorRun ()
#8 0x0816f58b in PortalSetResultFormat ()
#9 0x0816f8c7 in PortalRun ()
#10 0x0816da9f in PostgresMain ()
#11 0x08148b4e i
ably faster for my client application
to parse epoch from the database, though.
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
unset when the next connection
> uses the DB...
You mean other than setting "alarm 0;"?
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscri
27;s that. I'm not really sure why there's a need for a ping
-- and I've heard others question it, too.
Perl's DBI has a connect_cached() function that is suppose to return a
cached connection if it's still alive. So that is one place "ping" is
used. If ping fail
selects are always going to have something like WHERE
type = 'course' added onto the WHERE. That's extra processing for no
good reason.
> I'm sure there are many ways to get there. To me, the way I've
> described is the most-direct way to represent the relationships y
us classes. There also needs to be a link between the two.
For example, you might want to show a list of courses, and then see
what classes are scheduled for a given course, so a class should
reference its parent course.
Thanks very much,
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: explain analyze is your friend
is not allowing SELECTS.
What I did in my code was if ping fails, call rollback and then try
ping one more time. But, I'm not clear if that works in a more
general case or what might happen if the connection really is broken.
--
Bill Moseley
[EMAIL PROTECTED]
On Tue, Aug 16, 2005 at 08:25:25PM +0200, Martijn van Oosterhout wrote:
> On Tue, Aug 16, 2005 at 11:05:35AM -0700, Bill Moseley wrote:
> > I've read over the docs on Concurrency Control but still not clear
> > about when transactions block other updates, and how to deal
until either a
COMMIT or ROLLBACK.
Is it common for applications using Postgresql to set a timer on
updates and abort?
--
Bill Moseley
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
so I'm thinking of just manually updating all the
rows in sequence to adjust the order when needed -- to make things a
bit more simple. But it is a problem that I am curious about how best
to solve in a scalable way.
Thanks very much for your feedback.
--
Bill Moseley
[EMAIL PROTECTED
e SELECT and then *only* do
an UPDATE if the SELECT returns at least one row.
So, I should do:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Before the SELECT. And when I UPDATE I need to be prepared to do a
ROLLBACK if I get an error and repeat the process. (And, I assume,
take some precauti
up in the sort order relative to others.
SELECT id FROM __TABLE__
WHERE
sort_order <= (SELECT sort_order FROM __TABLE__ WHERE id = ?)
AND id != ?;
If that returns any rows then I know I can call the UPDATE to move the
item up.
Again,
71 matches
Mail list logo
