Hi,
I found this to create dynamic crosstabs (where the resulting columns
are not known beforehand): http://www.ledscripts.com/tech/article/view/5.html
(Thanks for Denis Bitouzé on
http://www.postgresonline.com/journal/index.php?/archives/14-CrossTab-Queries-in-PostgreSQL-using-tablefunc-contrib.h
Hi,
Which programming language are you using?
Regards,
Gevik Babakhani
PostgreSQL NL http://www.postgresql.nl
TrueSoftware BV http://www.truesoftware.nl
> -Original Message-
> Fro
Hi
By use # \l ,the 7.4.3 version database show
List of databases
Name| Owner | Encoding
---+--+---
testdbxx | userxxx | UNICODE
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(3 rows)
I think [some character codes may not have a c
Dean Gibson (DB Administrator) wrote:
Then I don't understand. While I've never used MySQL, the MySQL web
pages apparently indicate that VARCHAR has been supported since version
3.2: http://dev.mysql.com/doc/refman/4.1/en/char.html
Only in DDL and not the cast function, apparently. Mysq
"Adam Rich" <[EMAIL PROTECTED]> writes:
>>> I have two options for storing this data: As BYTEA or as large objects.
> Is it true that if you update a row containing a large BYTEA value, (even if
> you're not updating the BYTEA field itself, just another field), it requires
> the entire BYTEA valu
On 2008-02-12 19:39, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
On 2008-02-12 16:17, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
...
I'm guessing you declare an explicit length of 1 (for portability),
or do you "CAST (x as char)"? And one might ask in what context
> > I have two options for storing this data: As BYTEA or as large objects.
Is it true that if you update a row containing a large BYTEA value, (even if
you're not updating the BYTEA field itself, just another field), it requires
the entire BYTEA value to be copied to a new row (because of MVCC)
Dean Gibson (DB Administrator) wrote:
On 2008-02-12 16:17, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
...
I'm guessing you declare an explicit length of 1 (for portability), or
do you "CAST (x as char)"? And one might ask in what context we'd need
CHAR(1) on a numeric type, or
Koen Vermeer wrote:
Hi,
I would like to store binary data in a PostgreSQL database. The size of
the data is about 2 to 20 MB and is always stored or retrieved as a
block (i.e., I do not need to get only part of the data). As I
understand, I have two options for storing this data: As BYTEA or as
On 2008-02-12 16:17, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
...
I'm guessing you declare an explicit length of 1 (for portability), or
do you "CAST (x as char)"? And one might ask in what context we'd need
CHAR(1) on a numeric type, or else if substr/ing or left() make the
Tom Lane wrote:
Simply that a commonly used database (my) does not support it.
They do support char(n) in this context, which would have the advantage
of being standards compliant as well as de-facto portable.
Hmm, interesting. Mysql actual returns:
select cast(123 AS char(10)) -> '123' (
Ken Johanson <[EMAIL PROTECTED]> writes:
>>> What is wrong with using VARCHAR for your
>>> purpose
> Simply that a commonly used database (my) does not support it.
They do support char(n) in this context, which would have the advantage
of being standards compliant as w
> Hi,
>
> I would like to store binary data in a PostgreSQL database. The size of
> the data is about 2 to 20 MB and is always stored or retrieved as a
> block (i.e., I do not need to get only part of the data). As I
> understand, I have two options for storing this data: As BYTEA or as
> large obj
Dean Gibson (DB Administrator) wrote:
> On 2008-02-12 07:30, Ken Johanson wrote:
>>>
>>> Sure, but you're a prime candidate for understanding the value of
>>> following the spec if you're trying to write software that works with
>>> multiple databases.
>>
>> The spec has diminished in this (CAST w
On Tue, 12 Feb 2008 17:13:15 -0500
Tom Lane <[EMAIL PROTECTED]> wrote:
> Robert Treat <[EMAIL PROTECTED]> writes:
> > Of course we would still need to add an EOL page... I think one
> > could make a strong argument for a static url for EOL info now
> > that windows is EOL for < 8.2.
>
> You coul
On Sun, 2008-02-10 at 03:08 -0500, Lew wrote:
> SunWuKung wrote:
> > I always thought that having nullable columns in a table is a Bad
> > Thing (http://technet.microsoft.com/en-us/library/ms191178.aspx) and
>
> Ridiculous. The argument provided in that article is specious and likely SQL
> Serve
Tom Lane wrote:
> Robert Treat <[EMAIL PROTECTED]> writes:
> > Of course we would still need to add an EOL page... I think one could make
> > a
> > strong argument for a static url for EOL info now that windows is EOL for <
> > 8.2.
>
> You could make a strong argument for a page stating that
Hi,
I would like to store binary data in a PostgreSQL database. The size of
the data is about 2 to 20 MB and is always stored or retrieved as a
block (i.e., I do not need to get only part of the data). As I
understand, I have two options for storing this data: As BYTEA or as
large objects. As I un
On 12-Feb-08, at 5:05 PM, Tom Lane wrote:
Dave Cramer <[EMAIL PROTECTED]> writes:
The other process is inserting into the user_profile table.
Did either transaction do anything else in the same transaction
previously?
It would appear that the insert is running before the reindex starts.
Robert Treat <[EMAIL PROTECTED]> writes:
> Of course we would still need to add an EOL page... I think one could make a
> strong argument for a static url for EOL info now that windows is EOL for <
> 8.2.
You could make a strong argument for a page stating that versions
thus-and-so are *already
Ivan Sergio Borgonovo wrote:
> On Tue, 12 Feb 2008 16:15:23 -0300
> Alvaro Herrera <[EMAIL PROTECTED]> wrote:
>
> > Ivan Sergio Borgonovo wrote:
> >
> > > Is it just vaporware... maybe... but still there are pros and
> > > cons of having a bland schedule for EOL and new releases.
> >
> > We do h
Dave Cramer <[EMAIL PROTECTED]> writes:
>>> The other process is inserting into the user_profile table.
>>
>> Did either transaction do anything else in the same transaction
>> previously?
>>
> It would appear that the insert is running before the reindex starts.
That's not possible --- if i
Nathan Wilhelmi <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Insert statements would naturally take RowExclusiveLock, but that
>> doesn't block other DML operations. So the question is what *else*
>> are you doing?
> Good question, is there anyway to figure out which rows are locked?
RowExcl
On Tuesday 12 February 2008 15:48, Ivan Sergio Borgonovo wrote:
> On Tue, 12 Feb 2008 16:15:23 -0300
>
> Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > Ivan Sergio Borgonovo wrote:
> > > Is it just vaporware... maybe... but still there are pros and
> > > cons of having a bland schedule for EOL and n
On Tue, 12 Feb 2008 16:15:23 -0300
Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> Ivan Sergio Borgonovo wrote:
>
> > Is it just vaporware... maybe... but still there are pros and
> > cons of having a bland schedule for EOL and new releases.
>
> We do have a schedule:
> http://developer.postgresql.o
Tom Lane wrote:
Nathan Wilhelmi <[EMAIL PROTECTED]> writes:
Hello - Trying to track down a lock contention problem, I have a process
that does a series of select / insert operations. At some point the
process grabs a series of RowExclusiveLock(s) and has the obvious effect
of stalling othe
This is my table:
CREATE TABLE item_names
(
item_name character varying(255) NOT NULL,
culture_id integer NOT NULL,
item_id integer NOT NULL,
ft_idx_config regconfig,
CONSTRAINT pk_item_names PRIMARY KEY (item_id, culture_id)
)
Basically the table stores strings with varying language (a
On Tuesday 12 February 2008 10:26, Tom Lane wrote:
> Richard Huxton <[EMAIL PROTECTED]> writes:
> > Oliver Weichhold wrote:
> >> Is there something like a Migration Guide from 8.2to
> >> 8.3 for tsearch2 users?
> >
> > Hmm - there was a blog posting recently that linked to a load of
> > migration s
Nathan Wilhelmi <[EMAIL PROTECTED]> writes:
> Hello - Trying to track down a lock contention problem, I have a process
> that does a series of select / insert operations. At some point the
> process grabs a series of RowExclusiveLock(s) and has the obvious effect
> of stalling other processes. I
On Tue, Feb 12, 2008 at 04:13:33PM +0100, Thomas Chille wrote:
> We are still using 8.1.4 because a database upgrade for us and our
> product is a hefty step wich involves a lot of customer databases. But
> if it could help we consider to upgrade to 8.1.11 or 8.3. What would u
> suggest?
Obviously
Hello - Trying to track down a lock contention problem, I have a process
that does a series of select / insert operations. At some point the
process grabs a series of RowExclusiveLock(s) and has the obvious effect
of stalling other processes. I logged all the statements and don't see
any for up
Ivan Sergio Borgonovo wrote:
> Is it just vaporware... maybe... but still there are pros and cons of
> having a bland schedule for EOL and new releases.
We do have a schedule:
http://developer.postgresql.org/index.php/PostgreSQL_8.4_Development_Plan
--
Alvaro Herrera
On 2008-02-12 07:30, Ken Johanson wrote:
Sure, but you're a prime candidate for understanding the value of
following the spec if you're trying to write software that works with
multiple databases.
The spec has diminished in this (CAST without length) context:
a) following it produces an outp
SunWuKung wrote:
I always thought that having nullable columns in a table is a Bad
Thing (http://technet.microsoft.com/en-us/library/ms191178.aspx) and
Ridiculous. The argument provided in that article is specious and likely SQL
Server-specific. NULLable columns should occur wherever your da
On 12-Feb-08, at 1:02 PM, Gregory Stark wrote:
"Dave Cramer" <[EMAIL PROTECTED]> writes:
On 12-Feb-08, at 10:37 AM, Tom Lane wrote:
Alvaro Herrera <[EMAIL PROTECTED]> writes:
Dave Cramer wrote:
reindex table user_profile;
ERROR: deadlock detected
DETAIL: Process 32450 waits for AccessEx
"Dave Cramer" <[EMAIL PROTECTED]> writes:
> On 12-Feb-08, at 10:37 AM, Tom Lane wrote:
>
>> Alvaro Herrera <[EMAIL PROTECTED]> writes:
>>> Dave Cramer wrote:
reindex table user_profile;
ERROR: deadlock detected
DETAIL: Process 32450 waits for AccessExclusiveLock on relation
1
On 12-Feb-08, at 10:37 AM, Tom Lane wrote:
Alvaro Herrera <[EMAIL PROTECTED]> writes:
Dave Cramer wrote:
reindex table user_profile;
ERROR: deadlock detected
DETAIL: Process 32450 waits for AccessExclusiveLock on relation
194689112 of database 163880909; blocked by process 31236.
Process 31
On Tuesday 12 February 2008 16:03:31 Glyn Astill wrote:
> Thanks Mark,
>
> I've turned this on and I never see a DECLARE CURSOR so I presume I
> was wrong and it is not using cursors.
>
> I do see a DEALLOCATE though, although no PREPARE. Before this it
> does lots of statements that are limited
On Tue, 12 Feb 2008 11:19:19 -0500
Andrew Sullivan <[EMAIL PROTECTED]> wrote:
> On Tue, Feb 12, 2008 at 09:44:30AM +0100, Ivan Sergio Borgonovo
> wrote:
> > That's why I wrote "without making it too formal" and "bland
> > commitment to the release schedule...".
> The problem with doing it that w
On Feb 2, 2008 5:37 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> On Feb 2, 2008 5:51 AM, Rubén Rubio <[EMAIL PROTECTED]> wrote:
> >
> > Hi,
> >
> > Im trying to disable query statistics from log, and I am unable to do
> it.
> > Cannot find the correct option. I have been cheking google, postgres
"Peck, Brian" on February 11, 2008 1:31 pm
Confronted some .Net Development Issues
> Hey all,
>
> I'm trying to set up an ODBC connection inside Visual Studio
> 2005 and
> getting the error message "[Microsoft ODBC Driver Manager] Data source
> name not found and no default driver specified"
This may apply to Unix versions as well but I have not tried.
Anyway, I am using C# to create a GUI front end to pg_dump and I am
using the PGPASSWORD environment variable to set the password like this:
System.Environment.SetEnvironmentVariable("PGPASSWORD", myargs.password);
This works fine a
On Tue, Feb 12, 2008 at 09:44:30AM +0100, Ivan Sergio Borgonovo wrote:
>
> That's why I wrote "without making it too formal" and "bland
> commitment to the release schedule...".
The problem with doing it that way is that, when the release fails to meet
the original target deadline, any coverage o
--- Mark Cave-Ayland <[EMAIL PROTECTED]> wrote:
> Hi Glyn,
>
> In order to determine whether or not your driver is using cursors,
> the easiest
> way is to alter postgresql.conf so that individual SQL statements
> are
> recorded in the server log. You should then be able to see
> statements of
"Dave Cramer" <[EMAIL PROTECTED]> writes:
> reindex table user_profile;
> ERROR: deadlock detected
> DETAIL: Process 32450 waits for AccessExclusiveLock on relation 194689112 of
> database 163880909; blocked by process 31236.
> Process 31236 waits for AccessShareLock on relation 194689110 of d
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Can we rework REINDEX TABLE so that it processes each index on its own
> transaction?
It still wouldn't be guaranteed deadlock-free. There might be fewer
cases, but whether it would help Dave's particular case is just
speculation when we don't know wha
Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > I don't find this very surprising ... I would suggest using "reindex
> > index" for each index instead. I'm not sure if REINDEX TABLE is
> > supposed to be deadlock-free.
>
> It's not guaranteed to be so, but I'd think simple cases
Thomas Chille wrote:
Hi!
Some of our clients databases are performing less good after a while.
We are using autovacuum to vacuuming and analyzing the tables.
After some analyzes by my own it looks like that the tables or table
indexes are not analyzed or vacuumed fully or correctly.
You mig
Thomas Chille wrote:
> My 1. question is,
> if the known bugfixes for autovacuum after release 8.1.4 addressing my
> depicted issues?
Not directly, but keep reading.
> We are still using 8.1.4 because a database upgrade for us and our
> product is a hefty step wich involves a lot of customer dat
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Dave Cramer wrote:
>> reindex table user_profile;
>> ERROR: deadlock detected
>> DETAIL: Process 32450 waits for AccessExclusiveLock on relation
>> 194689112 of database 163880909; blocked by process 31236.
>> Process 31236 waits for AccessShareLock
Gregory Stark wrote:
"Ken Johanson" <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
SQL92 section 6.1 quoth
::=
CHARACTER []
| CHAR []
...
4) If is omitted, then a of 1 is implicit.
Therefore, writing just "char" is defined
Richard Huxton <[EMAIL PROTECTED]> writes:
> Oliver Weichhold wrote:
>> Is there something like a Migration Guide from 8.2to
>> 8.3 for tsearch2 users?
> Hmm - there was a blog posting recently that linked to a load of
> migration stuff...
There's always RTFM:
http://www.postgresql.org/docs/8.3/
On Tue, 12 Feb 2008, Mario Lopez wrote:
Hi!,
I optimized the LIKE 'keyword%' and LIKE '%keyword' with the following
results:
# time /Library/PostgreSQL8/bin/psql -U postgres -d testdb -c "select * from
table1 where varchar_reverse(data) like varchar_reverse('%keyword');"
real0m0.055s
u
Hi!
Some of our clients databases are performing less good after a while.
We are using autovacuum to vacuuming and analyzing the tables.
After some analyzes by my own it looks like that the tables or table
indexes are not analyzed or vacuumed fully or correctly.
A count(*) query takes multiple t
On Tue, Feb 12, 2008 at 03:45:51PM +0100, Mario Lopez wrote:
> the reversed index which takes like 20 minutes, I guess it has to do
> with the plperl function, perhaps a C function for inverting would make
> it up in less time.
sure. take a look at this:
http://www.depesz.com/index.php/2007/09/0
Mario Lopez wrote:
> The problem is still with the LIKE '%keyword%', my problem is that I am
> not searching for Words in a dictionary fashion, suppose my "data" is
> random garbage, that it has common consecutive bytes. How could I
> generate a dictionary from this random garbage to make it
On Tuesday 12 February 2008 14:21:35 you wrote:
> Hi chaps,
>
> We use a 3rd party driver to connect our some of our old ISAM
> applications into postgres (on linux), and as far as I'm aware the
> driver uses cursors.
>
> I've noticed that on some of our applications that read a lot of data
> the C
Hi!,
I optimized the LIKE 'keyword%' and LIKE '%keyword' with the following
results:
# time /Library/PostgreSQL8/bin/psql -U postgres -d testdb -c "select *
from table1 where varchar_reverse(data) like varchar_reverse('%keyword');"
real0m0.055s
user0m0.011s
sys 0m0.006s
# time
Dave Cramer wrote:
> reindex table user_profile;
> ERROR: deadlock detected
> DETAIL: Process 32450 waits for AccessExclusiveLock on relation
> 194689112 of database 163880909; blocked by process 31236.
> Process 31236 waits for AccessShareLock on relation 194689110 of
> database 163880909; b
Hi chaps,
We use a 3rd party driver to connect our some of our old ISAM
applications into postgres (on linux), and as far as I'm aware the
driver uses cursors.
I've noticed that on some of our applications that read a lot of data
the CPU usage for the postmaster serving it rockets up to between 8
reindex table user_profile;
ERROR: deadlock detected
DETAIL: Process 32450 waits for AccessExclusiveLock on relation
194689112 of database 163880909; blocked by process 31236.
Process 31236 waits for AccessShareLock on relation 194689110 of
database 163880909; blocked by process 32450.
jnj=
On Feb 12, 2008, at 2:55 AM, [EMAIL PROTECTED] wrote:
On Feb 11, 3:15 am, "${spencer}" <[EMAIL PROTECTED]> wrote:
I just literally ran my first search of the day and got the same
error.
i changed my query so that the integer was cast into text and then it
worked fine.
[EMAIL PROTECTED] wrote:
Oliver Weichhold wrote:
Hi
I run a site with several MediaWiki installations all running on PostgreSQL
8.2.5 utilizing TSearch2. Is there something like a Migration Guide from 8.2to
8.3 for tsearch2 users?
Hmm - there was a blog posting recently that linked to a load of
migration stuff...
H
"Ken Johanson" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>
>> SQL92 section 6.1 quoth
>>
>> ::=
>> CHARACTER []
>> | CHAR []
>>
>> ...
>>
>> 4) If is omitted, then a of 1 is implicit.
>>
>> Therefore, writing just "char" is d
Hi
I run a site with several MediaWiki installations all running on PostgreSQL
8.2.5 utilizing TSearch2. Is there something like a Migration Guide from 8.2to
8.3 for tsearch2 users?
Cheers
Oliver
Dave Page wrote:
On Feb 12, 2008 8:19 AM, Richard Huxton <[EMAIL PROTECTED]> wrote:
b) postgres database server will not install as an user with administrator
rights.
Indeed not. Or rather, it won't run as an administrator. I think you
should be able to run the installer as Administrator and te
On Feb 12, 2008 8:19 AM, Richard Huxton <[EMAIL PROTECTED]> wrote:
> > a) if your server is hosted in a dataceneter
> > for installation on windows 2000 you cannot use terminal service.
>
> Hmm - this *should* be documented somewhere. I don't really use Windows
> and I know about it somehow.
It is
On Mon, 11 Feb 2008 17:53:06 -0600
Decibel! <[EMAIL PROTECTED]> wrote:
> The problem with that is that as a volunteer-run project, dates
> can be off by a mile. Less than a year ago the plan was to release
> 8.3 is August-September 2007. Instead it was released a week or two
> ago.
That's why I w
Marc Munro wrote:
Can someone please tell me how to extract the mix, max, increment by,
etc, values for a sequence from the system catalogs. Is this in the
manual somewhere (I couldn't find it)?
Thanks
__
Marc
---(end of broadcast)---
TIP 1: i
INDIANNIC-HOSTING wrote:
we have written below a few lines which may help others. this applies to
win2k and also apply to win2003
Always good to have someone take the time to document their experiences.
a) if your server is hosted in a dataceneter
for installation on windows 2000 you cannot
On Mon, Feb 11, 2008 at 10:36:49PM -0700, Ken Johanson wrote:
> For sake of interoperability (and using an API that requires String-type
> hashtable keys), I'm trying to find a single CAST (int -> var/char)
> syntax that works between the most databases. Only char seems to be a
> candidate, but
71 matches
Mail list logo