On 07/12/2017 11:25 PM, Tom Lane wrote:
rihad <ri...@mail.ru> writes:
What if only English letters are used in the textual indices (ascii
0-127), would they still be impacted after datctype
"C"->"en_US.UTF-8" change?
Yes, as even minimal testing would have told yo
On 07/12/2017 11:25 PM, Tom Lane wrote:
rihad <ri...@mail.ru> writes:
What if only English letters are used in the textual indices (ascii
0-127), would they still be impacted after datctype
"C"->"en_US.UTF-8" change?
Yes, as even minimal testing would have told yo
On 07/12/2017 09:31 PM, Tom Lane wrote:
rihad <ri...@mail.ru> writes:
On 07/12/2017 01:54 PM, Albe Laurenz wrote:
As you see, your index is still sorted according to the C collation
and scanning it returns wrong results.
This ordering issue can certainly be classified as an inconsi
On 07/12/2017 09:31 PM, Tom Lane wrote:
rihad <ri...@mail.ru> writes:
On 07/12/2017 01:54 PM, Albe Laurenz wrote:
As you see, your index is still sorted according to the C collation
and scanning it returns wrong results.
This ordering issue can certainly be classified as an inconsi
On 07/12/2017 01:54 PM, Albe Laurenz wrote:
rihad wrote:
Hi there. We have a working database that was unfortunately created by
initdb with default ("C") collation & ctype. All other locale specific
settings have the value en_US.UTF-8 in postgresql.conf. The database
itself i
Hi there. We have a working database that was unfortunately created by
initdb with default ("C") collation & ctype. All other locale specific
settings have the value en_US.UTF-8 in postgresql.conf. The database
itself is multilingual and all its data is stored in UTF-8. Sorting
doesn't work
On 07/11/2017 12:45 AM, Tom Lane wrote:
rihad <ri...@mail.ru> writes:
On 07/10/2017 11:07 PM, Tom Lane wrote:
... which that isn't. I'd suggest checking for indexes that might need
to be rebuilt with this query borrowed from the regression tests:
I ran the query on our production da
On 07/10/2017 11:07 PM, Tom Lane wrote:
rihad <ri...@mail.ru> writes:
On 07/10/2017 08:42 PM, Tom Lane wrote:
No, your indexes on text/char/varchar columns will be corrupted
(because their sort order will now be wrong). If you can reindex
them before doing anything more with the da
On 07/10/2017 08:42 PM, Tom Lane wrote:
rihad <ri...@mail.ru> writes:
Hi there. We have a working database that was unfortunately created by
initdb with default ("C") collation & ctype. All other locale specific
settings have the value en_US.UTF-8 in postgresql.conf.
Hi there. We have a working database that was unfortunately created by
initdb with default ("C") collation & ctype. All other locale specific
settings have the value en_US.UTF-8 in postgresql.conf. The database
itself is multilingual and all its data is stored in UTF-8. Sorting
doesn't work
As PostgreSQL stores timestamps with a fractional part, does it mean that
WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012'04-23 23:59:59' might miss
records with values of f equal to 23:59:59.1234 or so?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to
On 04/24/2012 07:51 PM, rihad wrote:
As PostgreSQL stores timestamps with a fractional part, does it mean that
WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012'04-23 23:59:59' might
miss
records with values of f equal to 23:59:59.1234 or so?
Answering to myself: depends on how timestamp
Hi, I'm backing up the entire server directory from time to time.
pg_xlog/ directory containing WAL files is pretty heavy
(wal_level=archive). Can I exclude it from the regular tar archive?
#!/bin/sh
renice 20 $$ 2/dev/null
pgsql -U pgsql -q -c CHECKPOINT postgres # speed up
Hi, all. I'm looking for a way to lock on an arbitrary string, just how
MySQL allows doing with GET_LOCK('foo.123') / RELEASE_LOCK('foo.123'). I
know that at least Postgres 8.3 has pg_advisory_lock() /
pg_advisory_unlock() but they seem to accept integer values only, and
we're already using
On 5/04/2011 5:42 PM, rihad wrote:
Hi, all. I'm looking for a way to lock on an arbitrary string, just how
MySQL allows doing with GET_LOCK('foo.123') / RELEASE_LOCK('foo.123'). I
know that at least Postgres 8.3 has pg_advisory_lock() /
pg_advisory_unlock() but they seem
On 04/05/2011 08:29 PM, Ben Chobot wrote:
On Apr 5, 2011, at 7:35 AM, rihad wrote:
No, what I meant was that we're already using ints for a different
purpose in another app on the same server, so I cannot safely reuse
them. Aren't advisory lock ID's unique across the whole server? The
sole
On Tue, Apr 5, 2011 at 10:35 AM, rihad rihad(at)mail(dot)ru wrote:
No, what I meant was that we're already using ints for a different purpose
in another app on the same server, so I cannot safely reuse them. Aren't
advisory lock ID's unique across the whole server? The sole purpose
On 04/06/2011 12:20 AM, Vick Khera wrote:
On Tue, Apr 5, 2011 at 2:49 PM, rihad ri...@mail.ru
mailto:ri...@mail.ru wrote:
Can't do that, because I'm simply using some table's serial value as
the lock ID, which is itself a bigint.
So you assigned the entire namespace to the other
Hello,
In this query:
UPDATE foo
SET allocated_to=?
WHERE id=(SELECT MIN(id) FROM foo WHERE allocated_to IS NULL)
AND allocated_to IS NULL
RETURNING id
Is it guaranteed in any way that there will only be one id allocated and
returned even if multiple clients are executing this query
On 05/23/2010 03:15 PM, Grzegorz Jaśkiewicz wrote:
every single query in postrgresql runs as a transaction, on top of it,
some are atomic, like when you use RETURNING statement. This is
because postgresql doesn't actually have to select these rows as
separate query.
Please note the cooperation
On 05/23/2010 08:19 PM, Tom Lane wrote:
=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?=gryz...@gmail.com writes:
find in docs part that talks about transaction isolation levels, and
translate it to your problem.
Yes, please read the fine manual:
http://www.postgresql.org/docs/8.4/static/mvcc.html
What
On 05/24/2010 01:29 AM, Grzegorz Jaśkiewicz wrote:
don't lock tables explicitly. That's a killer for (concurrent) performance.
Just write queries properly, and use appropriate transaction level.
And you are sorted.
Read Committed is fine, as long as I restart the UPDATE query RETURNING
nothing.
rihad wrote:
Due to lack of support for partial (conditional) multi-column foreign
keys in 8.3, can before-triggers be used to implement them in terms of
data consistency and speed?
Let me clarify the question in semi-pseudo-SQL:
table foo {
bar_id int not null;
baz_id int not null
Hello,
Due to lack of support for partial (conditional) multi-column foreign
keys in 8.3, can before-triggers be used to implement them in terms of
data consistency and speed?
Thanks.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
sol= \d stats;
Table public.stats
Column| Type | Modifiers
--++---
id | integer| not null
start_time | timestamp(0) without time zone | not null
...
Adam Rich wrote:
This query from the console:
select * from stats order by start_time;
takes 8 seconds before starting its output. Am I wrong in assuming that
the index on start_time should make ORDER BY orders of magnitude
faster?
Or is this already fast enough? Or should I max up some memory
Hi,
I've come across a strange deadlock that I need your help with. There
are two copies of the same Perl daemon running on a 2 cpu box. The
program is pretty simple (because I wrote it :)) so I can trace its
pathway fairly well: in it, there's a single LOCK table foo occurring
part way
Given this type query:
UPDATE bw_pool
SET user_id=?
WHERE bw_id=
(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
RETURNING bw_id
The idea is to single-threadedly get at the next available empty slot,
no matter how many such queries run in
Scott Marlowe wrote:
On Thu, Apr 3, 2008 at 10:44 AM, rihad [EMAIL PROTECTED] wrote:
Given this type query:
UPDATE bw_pool
SET user_id=?
WHERE bw_id=
(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
RETURNING bw_id
The idea is to single
rihad wrote:
Hi,
I've come across a strange deadlock that I need your help with. There
are two copies of the same Perl daemon running on a 2 cpu box. The
program is pretty simple (because I wrote it :)) so I can trace its
pathway fairly well: in it, there's a single LOCK table foo occurring
rihad wrote:
Given this type query:
UPDATE bw_pool
SET user_id=?
WHERE bw_id=
(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
RETURNING bw_id
Can you use a SERIALIZABLE transaction and avoid the explicit lock?
Not really. Since LOCKing
Scott Marlowe wrote:
Sure, but you have to trap that all the time. The solution using a
cycling sequence keeps you from ever seeing that (unless you managed
to check out all 9,999 other values while still getting the current
one. No locking needed, dozens of updaters running
Hi all,
The situation: there are users in one table, and their access statistics
in the other. Now I want to find users whose last access time was more
than one month ago. As I've only had to write quite simple queries
involving no sub-selects so far, I'd like to ask your opinion if this
one
If you want to support multiple encodings, the only safe locale choice
is (and always has been) C.
I should be ashamed for asking this, but would someone care to tell me
how encoding differs from locale?
My postgresql FreeBSD rcNG script reads:
Hi,
Should the usual dump/restore cycle be performed during the upgrade on
FreeBSD? Any minor backward-incompatible changes one should be aware of?
Thank you for the best work.
---(end of broadcast)---
TIP 5: don't forget to increase your free
Hi all,
The situation: there are users in one table, and their access statistics
in the other. Now I want to find users whose last access time was more
than one month ago. As I've only had to write quite simple queries
involving no sub-selects so far, I'd like to ask your opinion if this
one
Hi, all! Subj, as is. Any other pitfalls I should be aware of?
Please check the release notes for this information.
Could you please be more specific? I can't find the steps to jump from
8.3beta2 to 8.3rc1, only from 8.2 to 8.3.
http://www.postgresql.org/docs/8.3/static/release-8-3.html
Hi, all! Subj, as is. Any other pitfalls I should be aware of?
Thanks.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Dec 18 15:49:41 myhost postgres[29832]: [35-1] ERROR: 23505: duplicate
key value violates unique constraint foo_key
Dec 18 15:49:41 myhost postgres[29832]: [35-4] INSERT INTO foo
Dec 18 15:49:41 myhost postgres[29832]: [35-5](a,b,c)
Dec 18 15:49:41 myhost postgres[29832]: [35-7] VALUES
Ted Byers wrote:
--- rihad [EMAIL PROTECTED] wrote:
Dec 18 15:49:41 myhost postgres[29832]: [35-1]
ERROR: 23505: duplicate
key value violates unique constraint foo_key
Dec 18 15:49:41 myhost postgres[29832]: [35-4]
INSERT INTO foo
Dec 18 15:49:41 myhost postgres[29832]: [35-5]
(a,b,c
http://www.postgresql.org/docs/8.3/static/wal-configuration.html
Is it right that checkpoint_timeout means the amount of time up to which
you agree to lose data in the event of a power crash? What if I set it
to 1 hour (and bump checkpoint_segments accordingly), does it mean that
I'm willing
Hi, is there a way to get the difference in hours between two
timestamps? The HH{1,}:MM:SS format will do.
foo= select timestamp '20071211 00:00' - timestamp '20071210 00:01';
?column?
--
23:59:00
(1 row)
foo= select timestamp '20071211 00:00' - timestamp '20071209 01:00';
In postgresql.conf generated by initdb shared_buffers is set to 32MB
even though there was more available (see below; also ipcs shows
postgres is (and will be) the only shared memory user). Is this enough
or maybe it's less than ok? I don't know. What I do know is that MySQL
4.0.x uses 500-550
Hi, would full_page_writes=off increase the risk of inconsistency or db
corruption in 8.3 and FreeBSD 7?
fsync = on;
Definitely on, as single power outage after three years is guaranteed
to make your life interesting otherwise.
synchronous_commit = off;
Can be off in my case as I'm not doing
Given this table:
CREATE TABLE foo (
id integer primary key,
bar_id integer references bar (id)
);
and provided that bar.id is itself a PK, do I still need to create an
index on bar_id if often doing queries like:
SELECT MIN(id) FROM foo WHERE bar_id IS NULL;
Table foo will contain a
I've been reading the online docs, but... code like this somehow ends up
in an indefinite loop:
CREATE OR REPLACE FUNCTION foo() RETURNS int AS $$
DECLARE
timeout int;
day date;
BEGIN
day := current_date + 1;
LOOP
SELECT date+1 INTO day FROM days WHERE date=day OR EXTRACT(dow
Tom Lane wrote:
rihad [EMAIL PROTECTED] writes:
LOOP
SELECT date+1 INTO day FROM days WHERE date=day OR EXTRACT(dow
FROM day) IN (0,6);
EXIT WHEN NOT FOUND;
timeout := timeout + 86400;
END LOOP;
If the EXTRACT condition is true, then the SELECT will always
rihad [EMAIL PROTECTED] writes:
LOOP
SELECT date+1 INTO day FROM days WHERE date=day OR EXTRACT(dow
FROM day) IN (0,6);
EXIT WHEN NOT FOUND;
timeout := timeout + 86400;
END LOOP;
If the EXTRACT condition is true, then the SELECT will always succeed.
Isn't
Scott Marlowe wrote:
On Nov 9, 2007 5:17 AM, rihad [EMAIL PROTECTED] wrote:
Em Wednesday 07 November 2007 13:54:32 rihad escreveu:
May I, as an outsider, comment? :) I really think of ASC NULLS FIRST
(and DESC NULLS LAST) as the way to go. Imagine a last_login column that
sorts users that have
Em Wednesday 07 November 2007 13:54:32 rihad escreveu:
May I, as an outsider, comment? :) I really think of ASC NULLS FIRST
(and DESC NULLS LAST) as the way to go. Imagine a last_login column that
sorts users that have not logged in as the most recently logged in,
which is not very intuitive. I
Hi, I'm planning to use prepared statements of indefinite lifetime in a
daemon that will execute same statements rather frequently in reply to
client requests.
This link:
http://www.postgresql.org/docs/8.3/static/sql-prepare.html
has a note on performance:
In some situations, the query plan
rihad wrote:
Hi, I'm planning to use prepared statements of indefinite lifetime in a
daemon that will execute same statements rather frequently in reply to
client requests.
This link:
http://www.postgresql.org/docs/8.3/static/sql-prepare.html
has a note on performance:
In some situations
Tom Lane wrote:
rihad [EMAIL PROTECTED] writes:
I don't understand why postgres couldn't plan this:
SELECT foo.e, foo.f
FROM foo
WHERE pk=$1 AND b=$2 AND status='1' AND c = $3;
to be later executed any slower than
SELECT foo.e, foo.f
FROM foo
WHERE pk='abcabc' AND b='13' AND status='1
Simon Riggs wrote:
On Wed, 2007-11-07 at 16:05 +0100, Martijn van Oosterhout wrote:
On Wed, Nov 07, 2007 at 02:37:41PM +, Simon Riggs wrote:
Editing an application, you would be required to add the words NULLS
FIRST to every single ORDER BY and every single CREATE INDEX in an
application.
Is there any way to hardcode the NULL handling in an index (as per
http://www.postgresql.org/docs/8.3/static/indexes-ordering.html) so that
SELECT * FROM t ORDER BY foo automatically implies NULLS FIRST (and,
similarly so that SELECT * FROM t ORDER BY foo DESC automatically
implies NULLS
Should an index be used on a created_at timestamp column if you know you
will be using ORDER BY created_at ASC|DESC from time to time?
Yes.
Thanks. This is stated explicitly in 8.3 docs (as opposed to 8.2)
http://www.postgresql.org/docs/8.3/static/indexes-ordering.html
And you should use
You mean Postgres wouldn't *always* use created_at's index with such
access patterns on a big table (even if one exists):
select * from foo order by created_at desc;
No, it wouldn't necessarily, and that's a good thing. A full-table
indexscan can often be slower than a sort because of
Hi,
Should an index be used on a created_at timestamp column if you know you
will be using ORDER BY created_at ASC|DESC from time to time?
Thanks.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
http://www.postgresql.org/docs/8.2/interactive/indexes-intro.html
states that Indexes can moreover be used in join searches. Thus, an
index defined on a column that is part of a join condition can
significantly speed up queries with joins.
Does this mean that a condition like WHERE ... [AND]
Hi,
After installing Postgresql 8.3 beta1 on a fresh FreeBSD 6.2 (port
databases/postgresql83-server) initdb gives error:
# /usr/local/etc/rc.d/postgresql initdb
initdb: file /usr/local/share/postgresql/snowball_create.sql does not
exist
This means you have a corrupted installation or
Hi,
Does anyone have an idea how risky it is to start using 8.3b1 in
production, with the intention of upgrading to release (or newer beta)
as soon as it becomes available? Risky compared to running a release,
that is. Beta - release upgrades might be less tricky than 8.2 - 8.3.
Thank you.
Richard Broersma Jr wrote:
--- rihad [EMAIL PROTECTED] wrote:
UPDATE Foo foo
SET foo.baz_id=baz.id
FROM Baz baz LEFT JOIN Bar bar ON (foo.bar_id=bar.id)
WHERE bar.id IS NULL;
This query cannot work.
I know. So how do I do it efficiently?
Thanks.
---(end
Richard Broersma Jr wrote:
--- rihad [EMAIL PROTECTED] wrote:
UPDATE Foo foo
SET foo.baz_id=baz.id
FROM Baz baz LEFT JOIN Bar bar ON (foo.bar_id=bar.id)
WHERE bar.id IS NULL;
This query cannot work. Basiclly, you are trying to set the foo.baz_id =
baz.id for records in
foo that do not yet
Hi,
Say I want to update table Foo with data in table Bar iff left join
between Foo and yet another table Baz finds no match.
UPDATE Foo foo LEFT JOIN Bar bar ON (foo.bar_id=bar.id)
SET foo.baz_id=baz.id
FROM Baz baz
WHERE bar.id IS NULL;
ERROR: syntax error at or near LEFT
UPDATE Foo foo
How can I efficiently return the minimum/maximum of two given
expressions? Like SELECT MYMIN(a+b-c,d+e*f).
Thanks.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to
Can someone please explain to me why these two give different results?
The idea is to get the number of seconds past 00:00:00, so the second
one is obviously correct.
foo= select extract(epoch from current_time);
date_part
--
42023.026348
(1 row)
foo= select extract(epoch from
Michael Fuhr wrote:
On Sat, Sep 15, 2007 at 04:45:02PM +0500, rihad wrote:
Can someone please explain to me why these two give different results?
The idea is to get the number of seconds past 00:00:00, so the second
one is obviously correct.
They're both correct.
foo= select extract(epoch
Michael Fuhr wrote:
On Sat, Sep 15, 2007 at 04:45:02PM +0500, rihad wrote:
Can someone please explain to me why these two give different results?
The idea is to get the number of seconds past 00:00:00, so the second
one is obviously correct.
They're both correct.
foo= select extract(epoch
Hi all,
I have a column declared as timestamp without time zone that I vainly
want to insert a raw timestamp into (i.e. in the format returned by
Perl's or PHP's time()). I know of SQL NOW(), but I want to insert a
cooked timestamp from the outside most efficiently. How?
Thanks.
Hi, I have two columns start_time stop_time declared as TIME. I'd
like to compute the difference between the two times in seconds, all in db:
SELECT
(CAST(stop_time AS SECONDS) + 86400 - CAST(start_time AS SECONDS))
% 86400;
Unfortunately AS SECONDS causes parse error. Any hints? Thanks.
Hi, my table is defined as:
CREATE TABLE users (
id integer NOT NULL,
...
);
CREATE SEQUENCE users_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER SEQUENCE users_id_seq OWNED BY users.id;
Although it's just a more verbose way to say
create table users (id
71 matches
Mail list logo
