I have a large table with numerous indexes which has approximately doubled
in size after adding a column - every row was rewritten and 50% of the
tuples are dead. I'd like to reclaim this space, but VACUUM FULL cannot
seem to finish within the scheduled downtime.
Any suggestions for reclaiming th
I need to get an idea of how much WAL space will be required during a long
(many hours) pg_basebackup over a relatively slow network connection. This
is for a server that's not yet running PITR / streaming.
Any thoughts?
* *
check_postgres.pl (--action=autovac_freeze) recently complained that we
needed to run VACUUM FREEZE. Doing so generated a boatload of WAL files -
perhaps on the order of the of the database itself.
Is VACUUM FREEZE something that is normally handled by autovac? If so, how
would we approach findi
Is there a simple notation for comparing most columns in the new and old
records in a pl/pgsql trigger function? Something like
(new.b, new.c, new.d) = (old.b, old.c, old.d)
works to compare all the columns except 'a', but is fragile in that it
needs to be updated any time a column is added to t
yntax error at or near "["
LINE 1: ...udh,array[array['rms','http://www.example.com']]))[1]::text)...
^
It looks like it doesn't like the array subscript. What might I be missing?
So, one set to mark the parameter and one for the expression? It's
starting to look like Lisp. ^_^
Thanks!
______
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
x27;c'] || 'd'::TEXT;
?column?
---
{a,b,c,d}
(1 row)
The assumption that the second argument is an array constant seems
surprising.
__
*Mike Blackwell | Technical Analyst, Distribution Services/Roll
On Tue, Apr 25, 2017 at 12:53 PM, Tom Lane wrote:
> Yeah. The core problem here is that the parser has to disambiguate the
> || operator: is it "anyarray || anyelement" or "anyarray || anyarray"?
>
<...>
> Peeking at the contents of the literal would make the behavior very
> unpredictable/da
I have an existing table in an app, along the lines of:
CREATE TABLE foo (
name text,
address text,
some_numeric_info integer,
);
I now need to be able to associate additional information (e.g. printing
order) with each field. Is it a bad idea to use the (fully qualified)
field name as
More detail, as suggested.
I have an existing table in an app, along the lines of:
>>
>> CREATE TABLE foo (
>> name text,
>> address text,
>> some_numeric_info integer,
>>
>> );
>>
I essentially need another table
CREATE TABLE foo_printing_options (
field_name text,
print_order int not
I understand from one of our developers there may be issues using VIEWs
with Entity Framework and npgsql. Can anyone with some experience using
PostgreSQL in a .NET environment comment?
__
*Mike Blackwell | Technical
We have a need to check certain text fields to be sure they'll convert
properly to EBCDIC. A check constraint with a convert() was the initial
thought, but there doesn't seem to be a default conversion from UTF8 to
EBCDIC. Does anyone have an implementation they'd care to share, or
suggestions on
I have an older database that was created with SQL-ASCII encoding. Over
time users have managed to enter all manner of interesting characters,
mostly via cut and paste from Windows documents. I'm attempting to clean up
and eventually the database to UTF8. I've managed to find most of the data
th
postgres+|*
> | | webuser=U/postgres |
> (1 row)
>
>
______
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | S
ciated
__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
mike.blackw...@rrd.com
http://www.rrdonnelley.com
<http://www.rrdonnelley.com/>
* *
Interesting, but that assumes there's a value to use in the coalesce that
isn't a valid data value.
______
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 W
Ah. A pair of constraints. I see.
Thanks!
__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Try pg_class.reloptions?
__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
mike.blackw...@rrd.com
http
I have a pair of tables, and a third describing a many-to-many relationship
between them. Along the lines of:
CREATE TABLE a (a_id integer NOT NULL PRIMARY KEY);
CREATE TABLE b (b_id integer NOT NULL PRIMARY KEY);
CREATE TABLE x (a_id integer NOT NULL REFERENCES a(a_id) ON DELETE CASCADE,
b_id in
data?
______
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
mike.blackw...@rrd.com
http://www.rrdonnelley.com
<http://www.rrdonnelley.com/>
* *
http://www.postgresql.org/docs/9.1/static/functions-subquery.html
__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office
The manual section on the postmaster process has some info:
http://www.postgresql.org/docs/current/static/app-postgres.html
__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR
em,
rather than have using up time/disk for backup. Might there be a way to
tag those databases somehow so the backup script knows to skip them? I'd
rather not hard code the list in the script.
Thoughts?
__
missed a
'user-defined database level metadata' field somewhere. ^_^
Thanks,
______
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, I
hange.
I have use the system catalogs for several one time projects related to
foreign keys, including checking which fks have associated indexes defined.
______
*Mike Blackwell | Technical Analyst, Distribution Services/Ro
I'd like to temporarily disable autovacuum on a single database while it is
being loaded. Is there an easy way to do this?
______
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management
#x27;t stop
the applications/users attempting to access the database, to avoid them
grabbing another connection while I'm typing.
__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnell
The following are the relevant log entries from a recent crash of v9.1.1
running on an older RHEL Linux box. This is the first crash we've
experienced in a lot of years of running Pg. Any assistance in how to
determine what might have caused this is welcome.
--
2012-02-10 13:55:59 CST [15949]:
On Mon, Feb 27, 2012 at 11:00, Lionel Elie Mamane wrote:
> Hi,
>
> I'm trying to understand the "clean" way to determine whether the
> current role has UPDATE (or SELECT or DELETE or UPDATE) privileges on
> a specific table (or column). If I can do it in a way that is portable
> across different
p_order_ids" in database "mydb"
2012-02-27 13:05:35 CST [18400]: [2-1] @ LOCATION: do_autovacuum,
autovacuum.c:2022
______
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Do
Given a pair of tables:
create table a (
id serial,
stuff text,
more_stuff text
);
create table a_audit (
id serial,
old_record a,
new_record a
);
How can one alter the structure of table a? Attempting
ALTER TABLE a ADD COLUMN even_more_stuff text;
results in the message:
ERROR:
>
>
> works for me -- what version are you on?
>
> merlin
>
> --
>
> [wcs1459@aclnx-cisp01 ~]$ psql --version
> psql (PostgreSQL) 9.1.1
> contains support for command-line editing
>
>
> [wcs1459@aclnx-cisp01 ~]$ cat x
> create table a (
> id serial,
> stuff text,
> more_stuff text
> );
>
Not a bad idea. I'd need to convert existing data, but it'd be an excuse
to try out hstore. ^_^
Mike
* *
On Thu, Mar 8, 2012 at 11:08, Merlin Moncure wrote:
> On Wed, Mar 7, 2012 at 2:49 PM, Merlin Moncure wrote:
> > On a practical level, the error blocks nothing -- you can bypass it
> > tri
__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
mike.blackw...@rrd.com
http://www.rrdonnelley.com
<http://www.rrdonnelley.com/>
* *
On Wed, Mar 14, 2012 at 16:04, Ale
I'd like to switch to PITR backups, but have limited disk space. Is there
a way to get a ballpark estimate by monitoring a running system, without
actually creating the WAL files and risking filling a filesystem?
Mike
Could someone please explain to me why the following select does not
result in a syntax error? (9.0.3)
begin;
create table x( c1 integer , c2 integer);
create table y( c3 integer, c4 integer);
select * from x where c2 in ( select c2 from y where c4 = 2 );
rollback;
Mike
--
Sent via pgsql-g
Indeed.
__
Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
mike.blackw...@rrd.com
http://www.rrdonnelley.com
ment =
warning. This is 9.1.3 These are two separate databases running on the
same server (postgresql instance).
______
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wall
38 matches
Mail list logo
