After some changes on my DB I notice that where I used to have a large
pg_toast_X table, I now have a large pg_largeobject table.
Can't find an explanation of the difference between the two, could someone
enlighten me?
No takers?
Some background I've changed my TOAST type from EXTENDED to MAIN.
On Thu, Jul 28, 2011 at 10:50 AM, bubba postgres
wrote:
>
> After some changes on my DB I notice that where I used to have a large
> pg_toast_X table, I now have a large pg_largeobject table.
&
Hi,
According to the docs, the DEFAULT keyword lets you explicitly insert a
field's default value in an INSERT statement.
>From a db function, I'd like to force the use of default when an input
parameter is null. This syntax looks correct but fails to compile.
Any suggestions?
INSERT INTO publ
Thanks for the previous posts. I am in fact running 9.0 'nix and am unable
to find a way to embed DEFAULT within an expression.
I can always rewrite the function to call EXECUTE but that's not a very good
solution in this case because many of our db functions are code generated.
On Tue, Aug 9, 201
Good idea but you're right, if the default value is an expression such as a
the next serial value, it will need to be executed first. If Execute
doesn't return the interpreted value, I don't know of a way to make it
work..
On Tue, Aug 9, 2011 at 5:35 PM, Diego Augusto Molina <
diegoaugustomol...@
Hi,
I need to store large files (from several MB to 1GB) in Postgres database.
The database has multiple schemas. It looks like Postgres has 2 options to
store large objects: LOB and BYTEA. However we seem to hit problems with
each of these options.
1. LOB. This works almost ideal, can
>Large Objects and bytea are the only ways.
>
>If you want to pg_dump only certain large objects, that won't work as far
as I know (maybe using permissions and a non-superuser can help).
>
>You absolutely need to pg_dump parts of the database regularly?
>
>Yours,
>Laurenz Albe
It is n
Using files stored outside the database creates all sorts of problems. For
starters you lose ACID guaranties. I would prefer to keep them in database.
We did a lot of experiments with Large Objects and they really worked fine
(stored hundreds of LOBs ranging from a few MB up to 1GB). Postgres does a
re
>Yeah, a pg_dump mode that dumped everything but large objects would be
nice.
There is option -b for pg_dump which controls whether large objects are
dumped or no. The problem is that with option -b it dumps all large objects
regardless of what schema you requested it to dump using option -n.
Othe
Hi,
Does anyone know the maximum number of elements allowed in a
one-dimensional array? It looks like my script may add at least a
couple million, not sure if it will hit a limit...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http:
Has anyone seen any performance metrics comparing the Opteron 6-core
Istanbul class processor aginst the 4-core Xeon e5500 series
processor, esp running a dataase? Or has anyone compared these 2
processor options before ordering a Postgres server?
The 6 core processor should offer a clear edge
Has anyone seen any performance metrics comparing the Opteron 6-core
Istanbul class processor aginst the 4-core Xeon e5500 series
processor, esp running a dataase? Or has anyone compared these 2
processor options before ordering a Postgres server?
The 6 core processor should offer a clear edge
Hi,
I've encountered a serious Postgres bug which apparently has a fix
that hasn't been released in a patch. How or where can I look up its
status?
This is a serious bug- it basically renders RETURN QUERY useless in
any environment where you don't have the option of dropping an
Hi,
I'm trying to write a very simple function statement to select a
single integer field from a table and save it into an int array. For
some reason I can't seem to find the correct syntax:
CREATE TABLE sample (
id integer
);
and then within a function:
my_array int[];
my_array = SELECT A
On Fri, Dec 18, 2009 at 9:53 PM, Merlin Moncure wrote:
> On Fri, Dec 18, 2009 at 11:35 PM, Pavel Stehule
> wrote:
>> 2009/12/19 Postgres User :
>>> Hi,
>>>
>>> I'm trying to write a very simple function statement to select a
>>> single int
On Sat, Dec 19, 2009 at 6:56 AM, Merlin Moncure wrote:
> On Sat, Dec 19, 2009 at 1:05 AM, Postgres User
> wrote:
>>
>> BEGIN
>> SELECT array_agg(category_id) INTO cat_list FROM (
>> WITH RECURSIVE subcategory AS
>> (
>
Hi,
I have a function that returns a refcursor that I need to call from a
second function. In the second function, I'd like to read a column
value from each row. However, I'm having a problem accessing the rows
of the refcursor.
Can anyone point me to a working example of how to pull this off?
Great call, someone did hose the data. Oddly enough the circular
reference caused no problem when running the stand alone recursive SQL
(with clause).
On Sat, Dec 19, 2009 at 10:41 AM, Merlin Moncure wrote:
> On Sat, Dec 19, 2009 at 1:30 PM, Postgres User
> wrote:
>>
>> T
Hi,
I have a database that was created with SQL-ASCII encoding
(unfortunately). I ran pg_restore to load the struct and data into a
new database with UTF-8 encoding but no surprise- I'm seeing this
error for a number of tables:
pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequen
Hi all,
I have a couple different ways to get results out of my table
structure, but I'm unsure which way is faster or less expensive to
run:
Option 1: Query a table of 4 million rows, on 4 indexed columns. It
will return 1 row:
SELECT field1, field2, field3, field4
FROM tablea
WHERE field1 =
uot; them on the secondary one. I have stopped postgres,
recreated "recovery.conf", started postgres and I get the following error:
LOG: database system was shut down at 2005-05-23 05:19:34 PDT
LOG: starting archive rec
Hi,
I'm opening a refcursor in Postgres to return a dataset to the client.
However, before returning the cursor, I'd like to iterate thru the
rows. Here's the code:
DECLARE
ref_entry refcursor;
rec record;
i integer = 0;
v_list varchar = '';
BEG
Thanks for the pointer. According to the Postgres docs:
"The portal name used for a cursor can be specified by the programmer
or automatically generated. To specify a portal name, simply assign a
string to the refcursor variable before opening it."
So I added the foll
If you need to return a record to another function or client program,
you can always use this sytnax:
SELECT var_a::integer, var_b::integer, var_c::integer... etc
where var_a, var_b, etc and local variables that hold your calculated values.
You dont need to create a local record structure- sele
I had tried several variations of MOVE Backward inside an Execute
statement earlier. And now, I'm seeing this error appear again:
ERROR: 0A000: cannot manipulate cursors directly in PL/pgSQL
I updated the Postgres function below with this replacement line:
Execute 'MOVE Backward All
select mt.fieldlist ' || new_fields || ' from mytable mt into rec2';
end;
This is a hack but it should work. You'll end up with a single record
var with your old fields and new fields, typed as you have specified.
On 3/1/07, Eddy D. Sanchez <[EMAIL PROTECTED]> wrote:
Good
Hi,
I'm using this code to increment a counter table:
IF Exists (SELECT keyfld FROM counter_tbl WHERE key_fld = 'key_val') THEN
UPDATE counter_tbl SET counter_fld = counter_fld + 1
WHERE key_fld = 'key_val';
ELSE
INSERT INTO counter_tbl(key_fld, counter_fld) VALUES('key_val', 1)
wrote:
Postgres User wrote:
> I'm using this code to increment a counter table:
>
> IF Exists (SELECT keyfld FROM counter_tbl WHERE key_fld = 'key_val') THEN
>UPDATE counter_tbl SET counter_fld = counter_fld + 1
> WHERE key_fld = 'key_val';
> ELSE
Marcel,
A sequence represents a unique identifier. You can call the function
'nextval' to get the next unique value in the sequence. See related
functions here:
http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html
In this code, I get the next sequence, insert it into a table,
e the partion in Postgres 8.2.3 so I want to
do some test for my awareness.
I create 2 tables:
The parent table is cities:*/
CREATE TABLE xxx.cities
(
id serial NOT NULL,
name text,
population real,
altitude integer,
CONSTRAINT pk_cities PRIMARY KEY (id)
)
/*and a partion of cities is capital
I recently heard from Josh Berkus that at least one major CMS
application uses Postgres to store entire HTML pages (including image
files) in order to support full versioning.
As a general rule, I prefer not to store BLOBS in a DB- I'd rather
leave the BLOB in the file system and let the db
Oleg,
This looks like a great module, do you have a pointer to it in English?
If can send this module to me as a compressed file, I'll take the time
to post it on PgFoundry as a new project that everyone can easily
access and download.
Paul- if you go with the lower() edits route, be sure to no
Is there a way to grant INSERT and UPDATE permissions on all the
tables in a database? I don't want to type-in every table name...
---(end of broadcast)---
TIP 6: explain analyze is your friend
Andrus,
As a C# developer myself, I'd recommend learning pl/pg sql for
writring stored procs. It's designed specifically for the kinds of
iterations and other operations you need when hanlding a recordset.
It's nothing like transact-sql of SQL Server.
If you must have full C# integration, then
You'll need to create a custom function in Postgres to support this,
which is fairly easy. It's been done before- do a search on Google:
http://www.hclausen.net/psql.php
On 5 Apr 2007 01:27:15 -0700, marcel.beutner <[EMAIL PROTECTED]> wrote:
Hello,
Thanks a lot for your answ
Hello, there! I recently migrated a medium-sized db from SQL Server to
postgres. A couple months after the migration, I noticed I was having
some problems retrieving some data via the JDBC driver. The problem
turned out to be multi-byte characters (smart quotes, etc) in the
SQL_ASCII database
Hi all!
I posted this on pgsql-performance but got no reply, so here it is:
thanks!
ps
-- Forwarded message --
From: Postgres Learner <[EMAIL PROTECTED]>
Date: Mon, 6 Dec 2004 14:58:46 +0530
Subject: 8.0 vs. 7.4 benchmarks
To: [EMAIL PROTECTED]
Hi all!
Has anyone do
table with ~90k
rows at one such time of weird behavior(they ran for a long long
time).
Is it possible that pg_autovacuum is auto vacuuming that same table at
that time and there is some unnecessary waiting involved because the
table is locked?
I am a complete newbie to postgres and I am throwing dar
Hi All!
I tried to find out more details about this weird problem, and I must
say, I am stumped.
here are the details:
We run a software module on a 64-bit opteron with a 32 Gig RAM, RedHat
AS3 update 3, Linux 2.4.21. postgres 7.4.6
I dumped the postgres query logs and here are the numbers
hello,
I am interested in disabling a trigger from a transaction.
I am not want to disable the trigger globally but only for the current
transaction.
Can I do it somehow ?
thanks,
Razvan Radu
---(end of broadcast)---
TIP 5: Have you checked our exte
Hi,
Has anyone tried to install Postgres on Windows Sever 2003 version R2? R2 is actually shipping as a 'new' Microsoft product- it's basically an interim update to Windows Server (
http://www.microsoft.com/windowsserver2003/r2/whatsnewinr2.mspx).
I've installed Postgres
That's what I was afraid of... it's a new install of Win Server 2003 R2, so I can rule out any third party firewall. Windows Firewall is NOT installed. And I've installed Postgres on a Windows XP box behind the same router, so it's not a router-firewall issue.
It's
Hi All,
I have recently started evaluating Postgresql 7.4.2 to replace some *cough*
more proprietary database systems... Thanks to the _excellent_ documentation
(a point I cannot overemphasize) I was up and running in no time, and got a
first test application running on the native C interface.
Th
hi!
I'm not shure that syntax you have used is correct, I mean that better is:
select * from tablex where name LIKE '%test%';
or you can also try "~~" operator that is used in Postgres as synonym to
"LIKE":
select * from tablex where name ~~ '%test%&#x
Hi All!
Compiling Postgres-6.4 I came accross following option in ./configure
script:
" --enable-recode enable cyrillic recode support"
What is it for ? I wonder if it's intended to add capability of
translation between koi8 and win-1251 encofing, but I was unable to find
On Tue, 17 Nov 1998, Taral wrote:
> > >i've got this annoying problem : if you create a table with an uppercase
> > >name, postgres transforms it in lower case. After that, if you try to
> > >retrieve the primary keys for this table ( still using the uppercase
&
Hi all,
we are using Postgresql 6.4.2 on FreeBSD 2.2.8 and have a lot of
problems with suddenly dying backend processes. We have already
changed kernel parameters to get more shared memory and start the
postmaster with -B 1024. The postmaster's virtual memory limit is
currently 300MByte and our l
On Wed, December 29, 2010 10:59, John R Pierce wrote:
> On 12/29/10 4:34 AM, Leif Biberg Kristensen wrote:
>> Back when I used Windows, my favorite editor was EditPlus
>> (http://www.editplus.com/). It isn't free, but well worth the 35 bucks.
>
> other good choices are Notepad++ (free) and my perso
On Thu, May 28, 2009 01:23, Richard Huxton wrote:
> zxo102 ouyang wrote:
>> Hi there,
>> Thanks for your suggestions. I do have an application running on the
>> machine
>> all the time. In fact, the application keeps writing real-time
>> monitoring
>> data into the database. Based on my understandi
On Wed, June 3, 2009 12:59, Bill Moran wrote:
> In response to Jennifer Trey :
>
>> Hmm, I just noticed the same write behavior on my Windows Xp laptop but
>> the
>> values was a little less.
>> I even created an DB with one table and column and this still happened
>> when querying it.
>
> By "cre
On Wed, June 3, 2009 13:44, Jennifer Trey wrote:
> On Wed, Jun 3, 2009 at 10:59 PM, Bill Moran
> wrote:
>
>> In response to Jennifer Trey :
>>
>> > Hmm, I just noticed the same write behavior on my Windows Xp laptop
>> but
>> the
>> > values was a little less.
>> > I even created an DB with one tab
On Fri, October 9, 2009 09:06, Andrew Gould wrote:
> On Fri, Oct 9, 2009 at 10:15 AM, Joshua D. Drake
> wrote:
>> On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote:
>>> On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure
>>> wrote:
>>> >
>>> > The #1 tool you have at your disposal is the human br
On Tue, October 28, 2008 05:57, Tom Lane wrote:
> Sam Mason <[EMAIL PROTECTED]> writes:
>> On Tue, Oct 28, 2008 at 10:42:47AM +0100, Thomas wrote:
>>> An easy trick I have found to set postgres password: $ sudo passwd
>>> postgres, and now you can type a ne
general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Since the pg_hba.conf file is located in the /etc path, the pg_read_file
command fails with an error of
"could not open file "pg_hba.con
On Thu, March 18, 2010 12:29, Bill Moran wrote:
> In response to "Abraham, Danny" :
>
>> Hi,
>>
>>
>>
>> A process hangs forever.
>>
>> When using this query...
>>
>>
>>
>> SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
>>
>>pg_stat_get_backend_activity(s.backendid) AS current_que
On Thu, April 16, 2009 13:20, Bill Moran wrote:
> In response to Thomas Kellerer :
>
>> Bill Moran wrote on 16.04.2009 21:40:
>> > The goal here is that if we're going to encrypt the data, it should
>> > be encrypted in such a way that if an attacker gets ahold of a dump
>> > of the database, they
Hi!!
Somebody can help me!!!??? :)
Where can i get information about how to use the client for win32???
i wanna use it with Delphi or some stuff like that
very much thanks
__
Get Your Private, Free Email at http://www.hotmail.com
***
Sorry Alvaro, i sent to you the message ;P
Is the same way (from snapshot) that use Mysql on ZFS.
IF you don't change anything in the database, why it don't works?
Then you restart the service with the same path.
The problem it will be that you need to stop postgres BEFORE snapshot.
2008/9/27 Douglas McNaught <[EMAIL PROTECTED]>:
> On Sat, Sep 27, 2008 at 12:13 PM, Mark Mielke <[EMAIL PROTECTED]> wrote:
>> If
>> some parts of PostgreSQL are not performance bottlenecks, and they are
>> extremely complicated to write in C, and very easy to write in something
>> else common and s
How do you manage the wal in both servers?
The version kernel is the same in both?
Runs the same services?
Do you make some test with Posgresql only in both servers?
If the problem is the inter-CPU, i know you can specified the number
of processors
do you want to run dedicated to one process.
200
n the new.
In that way could be a configuration kernel.
do you make a test of hardware instead postgres?? if the hard give you
better numbers, so postgres have
the problem.
2008/10/10 Shane Ambler <[EMAIL PROTECTED]>:
> Bart Grantham wrote:
>>
>> a long story short: we&#
is from a shell script to be executed without human
>> intervention
>>
>> I guess something like:
>>
>> su postgres -c 'psql ...whatever > /tmp/my_databases.txt'
>>
>> but I don't know exactly to what extent I can format the output of this
erforming a VACCUM FULL? It locks the database for several
> hours, and that is not a solution.
>
> I guess a backup-restore would do the work but, isn't there a better way to
> do this without shutting down postgres?
>
> Thanks in advandce.
>
>
--
http://slony.blogspot.com/
sorry is in spanish, but is an exelent resource,
i hope it will be useful
reggards
2008/10/27 Abdul Rahman <[EMAIL PROTECTED]>:
> May any one support step by step procedure for the replication with slony-I
> in windows xp.
>
>
--
Emanuel Calvo Franco
Sysc
do you just read FAQ_Solaris in the docs. I don't remember at all, but
is explain some steps to run with SSL.
2008/11/1, Zdenek Kotala <[EMAIL PROTECTED]>:
> Hi,
>
> It is really strange. Is there any reason why you don't use integrated SSL?
> 64bit version is located in /usr/sfw/lib/64
> Try
>
>
To answer the question "why?".. I'm wanting to run multiple independent
database sessions of Postgresql on the same machine. I thought I'd just
copy pgsql.sh startup scripts and add the -D option to specifiy different
database locations and -p option to specify different tcp port
numbers. Howeve
101 - 166 of 166 matches
Mail list logo
