On 02/29/16 06:20, Vik Fearing wrote:
>
> Hmm. How are you not the owner of a temporary table?
After 'set session authorization ...'
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi folks,
I've been using large objects in 8.4.x for some time now and have noticed that
some operations on large objects appear to take an inordinate amount of time.
For example, the \lo_list command in psql seems slow for what it does (many
seconds before first printed line with only a few thou
Hi folks,
We've discovered (or possibly rediscovered?) a corruption when restoring large
objects.
If 'standard_conforming_strings = on' is set in our DB (which is required for
our app) then the piped restore method (e.g. pg_restore -O backup.dat | psql)
results in the large objects being corrupte
Hi Tom,
Tom Lane wrote:
> Bosco Rama writes:
>> We've discovered (or possibly rediscovered?) a corruption when restoring
>> large
>> objects.
>
>> If 'standard_conforming_strings = on' is set in our DB (which is required for
>> our app
Tom Lane wrote:
>
> So I'm not sure whether to fix it, or leave it as a known failure case
> in old branches. Comments?
I understand the reluctance to fool with stable code. I have zero insight
into your installed versions distribution and backward compatibility needs
so any comment I may have
Wim Bertels wrote:
>
> --user2
> SET SESSION AUTHORIZATION user2;
> \pset format latex
> \echo ECHO queries
> \o report/test_user2.tex
> \i structure/test_user2.sql
> "
>
> This doenst seem to work,
> as the ECHO queries output isnt written to the file (test_user2.tex)
Actions are performed as t
Wim Bertels wrote:
> On Wed, 2011-02-02 at 12:49 -0800, Bosco Rama wrote:
>> Wim Bertels wrote:
>> >
>> > --user2
>> > SET SESSION AUTHORIZATION user2;
>> > \pset format latex
>> > \echo ECHO queries
>> > \o report/test_user2.tex
>
Wim Bertels wrote:
>
> \qecho doenst interpret parameters it just echo text, in this case 'ECHO
> queries'
Seems like you had two problems and I didn't see any reference to the second
one initially. The first was the output of \echo going to the wrong place
which is fixed by using \qecho.
The s
Christine Penner wrote:
> I'm trying to do a select to find on a char field that has a single
> quote but getting errors. This is what I'm doing:
>
> Select * from f_buildings where B_STRET_LNE_CON='BERLETT\'S ROAD'
There are a couple of ways to do this in PG but the easiest and closest
to the s
Alan Acosta wrote:
> But i have a new problem there, i have no rows for update, i create new rows
> when the seats are sold, cause rarely the database knows the capacity of the
> bus, number of seats, even some bus doesn't have seat number 4 for example
> :p. So i cannot SELECT FOR UPDATE no existe
Michael Black wrote:
>
> Ok. What am I missing here? B_USER is a defined Group Role
>
> CREATE ROLE "B_USER"
You used double-quotes here. This will preserve case and any
non-standard identifier characters (spaces, punctuation, etc.)
> TO ROLE B_USER;
And, thus, you need to use them here. W
Matt Warner wrote:
> No luck:
>
> *** as postgres
> postgres=# GRANT all on function nvl(anyelement,anyelement) to public;
> GRANT
> postgres=#
>
> *** as unprivileged user
> offload=> select nvl(0,1);
> ERROR: function nvl(integer, integer) does not exist
> LINE 1: select nvl(0,1);
>
Matt Warner wrote:
>
> The function cannot be defined in the user's DB because "language C" is
> considered a security risk, so only the superuser can do that. Or that's
> what I get from reading anyway...
psql -U postgres -d user_db
will allow the superuser to then define the function in the us
Hey folks,
We're using PG 8.4.7 on two servers that are geographically
distant from each other. We run WAL-shipping replication
(i.e. constant recovery mode replication) between the two
servers. These are the only two servers involved in the
setup. When we do the 'fail-over' to swap the master
On 06/06/14 07:30, Arup Rakshit wrote:
>
> How would I select rows which has empty array for the field "team_ids" ?
The shortest way is to test against an empty array constant:
select id from reporting_groups where team_ids = '{}';
Or you can test using array_length(). Or you test against an
On 06/12/14 10:01, CS_DBA wrote:
>
> We would like to setup a hot standby server with a forced delay.
> Is it possible to setup a hot standby based ONLY on WAL shipping and NOT
> use streaming replication?
Yes it is. Though, I assume you mean 'forced max sync period' as
opposed to 'forced delay
On 07/03/14 10:04, David Wall wrote:
>
> A pg_dump backup -- with most of the data stored as large objects --
> takes about 5 hours.
If those large objects are 'files' that are already compressed (e.g.
most image files and pdf's) you are spending a lot of time trying to
compress the compressed d
On 07/03/14 16:51, David Wall wrote:
>
> On 7/3/2014 10:36 AM, Bosco Rama wrote:
>> If those large objects are 'files' that are already compressed (e.g.
>> most image files and pdf's) you are spending a lot of time trying to
>> compress the compressed data
On 07/03/14 17:00, John R Pierce wrote:
> On 7/3/2014 4:51 PM, David Wall wrote:
>> That's interesting. Since I gzip the resulting output, I'll give -Z0
>> a try. I didn't realize that any compression was on by default.
>
> default compression only happens in with pg_dump -Fc
Yeah. OP says h
On 07/03/14 17:30, David Wall wrote:
>
> Bosco, maybe you can recommend a different approach. I pretty much run
> daily backups that I only have for disaster recovery. I generally don't
> do partials recoveries, so I doubt I'd ever modify the dump output. I
> just re-read the docs about form
On 07/03/14 21:26, David Wall wrote:
> On 7/3/2014 6:26 PM, Bosco Rama wrote:
>> BTW, is there any particular reason to do the 'split'?
> Yes, I transfer the files to Amazon S3 and there were too many troubles
> with one really big file.
Is the issue with S3 or jus
On 07/03/14 22:51, David Wall wrote:
>
> It just seems odd that pg_dump is slower than pg_restore to me. Most
> grumblings I read about suggest that pg_restore is too slow.
>
> I have noted that the last split file segment will often appear to be
> done -- no file modifications -- while pg_dump
On 07/03/14 22:51, David Wall wrote:
>
> On 7/3/2014 11:47 AM, Eduardo Morras wrote:
>> No, there's nothing wrong. All transparent compressed objects stored
>> in database, toast, lo, etc.. is transparently decompressed while
>> pg_dump access them and then you gzip it again. I don't know why it
On 09/26/14 12:58, Nelson Green wrote:
>
> I am setting up a streaming replication stand-by, and the replication
> role password has a single quote in it. I am unable to properly
> reference the password in the conninfo setting of recovery.conf so it
> will authenticate to the master. Doubling the
On 01/12/15 08:40, Antony Gelberg wrote:
>> In addition are you talking about a single database or the Postgres database
>> cluster?
>
> We only have one database in the cluster, so it's the same thing.
Not exactly. For example, with pg_dump you would also need to dump the
'global' context separ
On 01/11/13 09:31, Gavin Flower wrote:
> -- theta in radians
> -- for radius = 100
>
> INSERT INTO ranpoint
> (id, theta, r)
> VALUES
> (generate_series(1, 10), pi() * random(), 100 * random());
Shouldn't the value for theta be:
2 * pi() * random()
Bosco.
--
Sent via pgsql-gene
On 05/31/13 14:37, David Salisbury wrote:
>
> I would think this would be possible. I'm on 9.0.8
IIRC, updatable CTE's don't appear until 9.1.x
HTH
Bosco.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/ma
On 05/31/13 15:33, David Salisbury wrote:
>
> And without trying too much ;), I'll bet there is no way to do this in SQL
> proper. i.e.
> I can't correlate an update with a select stmt, as in a correlated sub-query
> sort of way.
> So for this to work I would indeed need to write a function tha
On 05/31/13 16:27, David Salisbury wrote:
>
> It would seem related to the above to me, but apparently it's not.
> ---
> According to the standard, the column-list syntax should allow a list of
> columns to be assigned from a single row-valued expression,
> such as a sub-select:
>
On 06/06/13 15:35, Joshua D. Drake wrote:
>
> I had the distinct displeasure of staying up entirely too late with a
> customer this week because they upgraded to 12.04 and immediately
> experienced a huge performance regression. In the process they also
> upgraded to PostgreSQL 9.1 from 8.4. Th
On 06/25/13 06:13, Nicolau Roca wrote:
>
> 2013-06-25 15:02:15 CEST [::18264:1:] LOG: autovacuum: found orphan
> temp table "pg_temp_47"."est_backup_ids_temp" in database "estudis1314"
> 2013-06-25 15:02:15 CEST [::18264:2:] LOG: autovacuum: found orphan
> temp table "pg_temp_47"."est_backup_f
On 08/02/13 09:33, Melvin Call wrote:
>
> $ psql -U postgres
>
> DROP SCHEMA IF EXISTS hrschema CASCADE;
> DROP DATABASE IF EXISTS personnel;
> DROP USER IF EXISTS hr_admin;
>
> CREATE USER hr_admin
>WITH CREATEDB
>PASSWORD 'md5be394806d6a21c6c52aa2b76063c7d9d';
>
> DROP DATABASE IF EXI
On 08/02/13 10:56, Melvin Call wrote:
>
> If I may pigtail another related question, what is the procedure for
> allowing another user access to that schema?
Heh. You almost have the words already:
grant usage on schema hrschema to hr_user;
This will allow them to see the objects in the sche
On 09/12/13 15:07, Roberto Scattini wrote:
> hi bosco, in fact i believe that i have that problem... but i cant
> undestand why and how to fix it.
>
> the database has SQL_ASCII encoding, and my client... i am not sure, when i
> connect directly with psql from localhost i must set client encoding
On 09/12/13 14:49, Chris Curvey wrote:
> Is this just a case-sentitvity issue? if personas.apellido is a varchar
> field, then I think that's your trouble. (it would have to be citext in
> order for "nunez" = "NUNEZ".)
He's using 'ilike' in his query, so this is more likely to be a
locale/charse
On 11/08/13 09:30, Tim Kane wrote:
>
> Remi, to answer your question - this is being called as part of a shell
> script.
In that case your options expand greatly...
For example:
tbl_name="MM"
psql -c "alter table my_table rename to my_table_${tbl_name}_raw;"
or for more complex stuff
On 12/31/13 10:55, peterlen wrote:
> After logging into the PSQL Shell Script as a particular user and database, I
> want to display who my current user is and what database I am in as I may
> have forgotten who I am logged in as in order to make sure any commands are
> going to the right place. I
On 12/31/13 11:06, Bosco Rama wrote:
>
> Use the connect meta without arguments, i.e.
>
> xyz> \c
> You are now connected to database "xyz" as user "demo".
Hmmm. Scratch that. That actually does a reconnect. Probably not
what you want, especially is
On 04/09/14 14:46, Scott Marlowe wrote:
>
> I'm not deploying any new distro version that soon. :) I know folks
> just putting 12.04 into prod to replace etch and lenny. :)
You can easily get the 3.11.0 kernel on 12.04.4 LTS by installing
the linux-generic-lts-saucy package. IIRC, the fix for th
On 04/16/14 17:08, Susan Cassidy wrote:
> The function does a select to see if the id number exists, and it fails.
> NOT FOUND causes a RAISE EXCEPTION.
Is it returning the right id? I seem to remember a recent thread
about Perl DBI returning the wrong id's for certain operations.
Just at though
Hi folks,
We have a current DB on PG 8.4.7 on Ubuntu Server 10.04 LTS.
This DB used to only have one schema and that schema was replaced
on a regular schedule using a pg_dump/pg_restore process. The old
schema was renamed to another name and the incoming schema and data
replaced it in the DB. If
Hey folks,
Having not had to worry about character encoding in the past we
blithely used the SQL_ASCII encoding and had the application do
the input filtering. We have reached the point where we would
like to have the DB enforce the character encoding for us. We
have chosen to go with LATIN9 enc
Joshua Tolley wrote:
> On Thu, Apr 14, 2011 at 07:33:17PM -0700, Bosco Rama wrote:
>> 1) a function that removes/creates the views, etc in the 'xyz' schema
>>that gets called as part of the replacement process for schema 'abc'
>>
>> 2
Seb wrote:
> A query such as:
>
> SELECT 'a' || 'b' || NULL;
>
> returns a NULL. How can I ensure I get 'ab' in the result? I'm trying
> to concatenate columns and one of them might have NULL values, which I
> simply want to ignore.
COALESCE is your friend:
select 'a' || 'b' || COALESCE(col
Asfand Qazi (Sanger Institute) wrote:
>
> Say I have a table with fields 'template' and 'original_letter', and
> 'new_selected_letter'. 'template' could be 'abcdefg0abcdefg',
> original_letter could be 'A' and new_selected_letter could be 'B'.
>
> I want a view where I see 2 fields: 'original' a
Carlos Mennens wrote:
> I created a modifier for auto incrementing my primary key as follows:
>
> records=# \d users
> Table "public.users"
> Column | Type | Modifiers
> +---+-
Carlos Mennens wrote:
> On Tue, May 17, 2011 at 2:21 PM, Raymond O'Donnell wrote:
>> That's because of what I just mentioned above. :-) It's not a type: it's
>> just a shortcut. What you need to do instead is something like this:
>>
>> -- Create the sequence.
>> create sequence users_id_seq;
>>
Leif Jensen wrote:
>
> With that note in mind, how do I select from a table field of type 'text' ?
If the type is text and you have no idea how long it will be have ecpg/libpq
allocate a buffer for you by using a pointer set to NULL as the variable:
exec sql char *val = NULL;
/* Use an
Chrishelring wrote:
>
> CREATE OR REPLACE VIEW test AS
> SELECT
> right(cast('000' as text) || cast(road_number as text), 4) AS GEO_ADRESSE
>
> FROM rk_ois.bbrbygning
> WHERE ejerlav <> 0
>
> to ensure that the road_number would be a fixed size (four digitis).
> Unfortunately this doesn't
Leif Jensen wrote:
>
> This seems to be working most of the time, but looking at the generated C
> code from the ecpg compiler and the associated library functions, we are
> not sure whether we should put mutex locks around the 'select' part to
> avoid several threads are using "the same" execdesc
Leif Jensen wrote:
>
> Is it really not possible to use 2 separate connection within 1 thread
> at the same time ? or is it an error in the ecpg library ?
It should be entirely possible to run multiple connections in a single
thread as long as you manage the 'AT connName' clauses properly.
Thoug
Andy Chambers wrote:
> I'm confused about the correct syntax for updating an aliased table. I want
> to update triple from triple_updates
> where the data is different and tried to use the following
>
> update triple old
>set
> old.obl
Gauthier, Dave wrote:
> Well, I found a better way, but still open to suggestions.
>
> This is what I have so far...
>
> create temporary table foo as select * from maintable where 1-0; -- Credit 4
> this goes to a post in the PG archives
> insert into foo (select * from maintable where primcol
Leif Jensen wrote:
>
> Thank you for your comment. Yes, it would be nice to get some more
> comments on the allocate/deallocate on a connection issue.
>
> I have verified that in my case deallocating a prepared statement,
> it guesses the wrong connection and returns an error. (The right
> one is
Gauthier, Dave wrote:
>
> I'd like to pass a parameter into an inline psql call that itself
> calls an sql script, something like...
>
> psql mydb -c "\i thesqlscript foo"
>
> Where"foo" is the value I want to pass in.
You may want to use the --set or --variable options of psql and then
referen
Merlin Moncure wrote:
> On Tue, May 31, 2011 at 7:35 PM, Bosco Rama wrote:
>> Unfortunately, like you, I am just a user of this wonderful DB. Since
>> we are not seeing any other input here on the 'general' list it may be
>> time to move this thread to the
Hi folks,
I have a strange disparity between a query that is run as a
straight select and the same query via a cursor. I hope I can
jog someone's memory with the description as I have been unable
to create a sanitized and/or reduced data set & schema that will
reproduce this ... so far. :-(
Runn
Hi Tom,
Tom Lane wrote:
> Bosco Rama writes:
>> I have a strange disparity between a query that is run as a
>> straight select and the same query via a cursor. I hope I can
>> jog someone's memory with the description as I have been unable
>> to create a s
Bosco Rama wrote:
> Tom Lane wrote:
>>
>> Cursors are biased towards fast-start plans on the theory that you
>> may not be intending to fetch the whole result. Queries with ORDER BY
>> and/or LIMIT are particularly likely to see plan changes as a
>> consequenc
Hi,
Poul Møller Hansen wrote:
> I have just restored a database about 347GB in size
>
> postgres=# select * from pg_database_size('dbname');
> pg_database_size
> --
> 346782483256
>
> using this command:
> pg_restore -d dbname -O -Fc dbname.backup
>
> It started at 13/5
Hi Josh,
Josh Kupershmidt wrote:
>
> Using a recent pg_restore (tested with 9.1.2, verbose mode on) and a
> custom-format dump containing large objects, you should see messages
> like this:
>
> pg_restore: creating TABLE image
> pg_restore: executing BLOB 126810
> pg_restore: executing BLOB 1331
Hey Josh,
I found the message I was seeing. It was/is(?) in StartRestoreBlob() and it
looks like this:
ahlog(AH, 2, "restoring large object with OID %u\n", oid);
But I don't know how to find it in the current git tree or how to activate
it from the command-line (assuming it is still part of
h...@101-factory.eu wrote:
> thanks i thought about splitting the file, but that did no work out well.
>
> so we receive 2 files evry 30 seconds and need to import this as fast as
> possible.
>
> we do not run java curently but maybe it's an option.
> are you willing to share your code?
>
>
Emi Lu wrote:
> Good morning,
>
> Is there a simply method in psql to format a string?
>
> For example, adding a space to every three consecutive letters:
>
> abcdefgh -> *** *** ***
Depending on how you want 'extras' handled, you could work from
something like this:
select trim(regexp_repl
On 08/22/12 15:19, Gauthier, Dave wrote:
>
> I know a view can be used to alias one of them.
It can alias all of them:
create view xyz as
select *, last_name as lname, last_name as surname
from mytable;
(not the nicest version but functional)
HTH.
Bosco.
--
Sent via pgsql-general
Ooops! Hit send too fast...
On 08/22/12 15:34, Bosco Rama wrote:
> On 08/22/12 15:19, Gauthier, Dave wrote:
>>
>> I know a view can be used to alias one of them.
>
> It can alias all of them:
>
> create view xyz as
>select *, last_name as lname, last_name a
On 08/22/12 17:23, Mike Christensen wrote:
> I'd like to import this data into a Postgres database:
>
> http://www.ars.usda.gov/SP2UserFiles/Place/12354500/Data/SR24/dnload/sr24.zip
>
> However, I'm not quite sure what format this is. It's definitely not
> CSV. Here's an example of a few rows:
On 08/22/12 17:41, Mike Christensen wrote:
> On Wed, Aug 22, 2012 at 5:38 PM, Mike Christensen wrote:
>> On Wed, Aug 22, 2012 at 5:34 PM, Bosco Rama wrote:
>>> On 08/22/12 17:23, Mike Christensen wrote:
>>>> I'd like to import this data into
On 09/23/12 20:50, Arun R T wrote:
>
> I have some problem to connect PostgeSQL server from client mechine
[snip]
> org.postgresql.util.PSQLException: Connection refused. Check that the hostname
> and port are correct and that the postmaster is accepting TCP/IP connections.
Given this ^^^ follow
On 09/26/12 17:56, joao viegas wrote:
>
> does anybody knows how can I have something like:
> select function_that_returns_int(NEW.field1) into NEW.field2;
I think you want:
select into NEW.field2 function_that_returns_int(NEW.field1);
or even:
NEW.field2 := function_that_returns_int(NEW.f
On 10/26/12 11:50, Samuel Gilbert wrote:
>
>(SELECT to_char(42, '0') AS string) AS example;
>
>string | length | showthemtome
> ++--
> 00042 | 10 | " 00042"
Sign padding. Try using:
select to_char(42, 'FM0') ...
To see
Tom Lane wrote:
> Phoenix Kiula writes:
>> Hi. I'm using Postgresql 9.0.5, and the connection is made via pgbouncer.
>
> Perhaps pgbouncer is redirecting the second command to a different
> session?
This may be OT, but are temp tables also removed when setting a new session
authorization?
Bosco
Hi folks,
I'm using PG 8.4.10 on Ubuntu Server 10.04.3.LTS x86_64.
Is there any way to query the SSL mode for client connections?
E.g. select backend_id from some_table_or_view where ssl_mode = false;
I have not been able to find it in pg_stat_activity or anywhere else in
the catalog. Maybe I
ChoonSoo Park wrote:
> Each function returns key/value pairs from hstore type.
> Is there any opposite function that returns hstore type from key/value rows?
>
> I know hstore (text[], text[]) can construct it. Is there any other choice?
>
> I have a table with ID & Description columns and want t
Sry, forgot to add list.
Thom Brown wrote:
>
> I've done that a couple times, but no effect. I think Tom's point
> about a filesystem bug is probably right.
Have you rebooted since this started? There may be a process that is
holding the pid file 'deleted but present' until the process termina
Tom Lane wrote:
>
> Fascinating. So maybe there is something to Bosco's theory of something
> holding open the old pidfile.
There could also have been a corrupt in-memory/cached descriptor in the
filesystem code that never needed flushing to disk? That would help
explain why it fully went away
Bret Stern wrote:
>
> trying to update a varchar numeric string column
> by converting it to int, adding a numeric value and insert it back
> as a varchar
>
> Having trouble with cast
I assume you are doing an update as opposed to an insert. You use
both above (and both numeric and int as well)
Doug Gorley wrote:
>
> "select * from tdt_unsent where str_name_l = 'SMITH'" returns 0 rows.
> "select * from tdt_unsent where str_name_l ~ '^SMITH'" returns 3 rows.
> "select * from tdt_unsent where str_name_l ~ '^SMITH$'" returns 0 rows.
> "select length(str_name_l) from tdt_unsent where str_nam
haman...@t-online.de wrote:
>
> is there a way to use a table alias for the target table in an UPDATE query?
> I would like to avoid spelling out things like that in psql:
>
> update myverylongtablename set col1 = r.col1repl from repltable r
> where myverylongtablename.id = r.id and myverylongt
Hey,
Gauthier, Dave wrote:
> H... I don't have root access :-(
Do you have the ability to alter the users/roles? If so, you
could set their connection limit to 0 and then kick them off.
Do your work and then set their connection limit back to the
value it was before. The default is -1 (unli
Gauthier, Dave wrote:
> Hmmm This sounds like it might work.
> There are, in fact, only 2 users (roles). Lets call them "selectuser" and
> "moduser"
> So, as the DBA, I just...
>
> alter role selectuser connection limit 0;
> alter role moduser connection limit 0;
>
> Then kick everyone off
81 matches
Mail list logo
