Why not just try it! Answer: all -t switches after the first one are
ignored. (And, no, "pg_restore --help" does not mention that). However
with -l and -L, you have a much more powerful mechanism for specifying
exactly which objects you want restored.
> -Original Message-
> From: pgsql-ge
instead of redefining the table (and ending up with two tables pointing
to the same sequence) you could also just call nextval() on the target
sequence when inserting into your temp table -- pretty much the same
thing but seems a bit cleaner.
insert into adresses_temp
select
userid,
addressid,
> >> In the second place, the reason most of our messages don't already
> >> contain schema names is that in the past we've judged it would be
> >> mostly clutter; and given the infrequency of complaints I see no
> >> reason to change that opinion.
>
> > I tend to disagree. We can run a poll in a
This is minor, but just curious about the reasons for the assymetry
between ALTER and DROP with respect to tables vs. views.
* ALTER TABLE seems to work on both tables and views (even though ALTER
VIEW exists, albeit with a limited operation support).
* DROP TABLE works only on tables, not on view
> From: Greg Smith [mailto:[EMAIL PROTECTED]
> Sent: Monday, May 19, 2008 9:03 PM
>
> So, yes, in 8.3 it's possible that you can have sequential
> scans of large
> tables or the VACUUM data pass through the buffer cache, but
> not remain in
> it afterwards. I didn't think George would ever r
I am on PG 8.1.10 on RedHat Linux. Some page cache/query performance
questions:
Is there a way to tell whether a query is satisfied from memory cache or
from disk. The only way I know of is based on the time the query takes
(reported by EXPLAIN ANALYZE) -- comparing the first-time performance
(aft
I started seeing some negative durations in my production query logs --
a "-" sign preceding the duration number, e.g.:
% grep 'duration: -' postgresql-Wed-*
postgresql-Wed-09.log:2008-01-02 08:56:33 PST [11705]: [538-1] LOG:
duration: -268280.421 ms
postgresql-Wed-15.log:2008-01-02 15:01:01 PST [
> I am trying to create an expression which
> - always yield true if the incomming array is NULL
> - yields true if a given value is in the array, otherwise yields false
>
> I thought this should work:
> Select 'target'=ANY(COALESCE('{indata1, indata2}','{target}'))
>
> but I get an ERROR: op AN
> From: David Fetter [mailto:[EMAIL PROTECTED]
> On Tue, Sep 11, 2007 at 08:55:53AM -0700, George Pavlov wrote:
> > sum(case when z!=0 then 1 end) as good,
>
> This case statement returns true when z factorial is zero, so I'd
> recommend the SQL standard <>
select
to_char(ts, 'MM/DD/') as "day",
str,
proc,
sum(case when z!=0 then 1 end) as good,
sum(case when z =0 then 1 end) as bad
from foobar
where str != 9
group by 1,2,3
order by 1
;
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf
i am trying to create a temp table inside a plpgsql function (i just
need a temporary place to hold data, but it is too complex for any other
data structure). unfortunately if i call the function again within the
same session the temp table still exists and the function fails. if i
drop the temp ta
as everyone has pointed out it does not seem like the best table design
and querying for these fields as normal course of business does not seem
that great, but if you wanted to audit tables like these once in a while
you could easily do it using your favorite scripting language or SQL
itself. here
What's the plan for releasing the next 8.1? There hasn't been a release
since April and there have been fixes. (I personally am particularly
interested in "implement chunking protocol for writes to the syslogger
pipe" because without it over 2/3 of attempts at query analysis fail for
me).
George
> With DROP CASCADE, he can get rid of
> everything within
> the schema at a blow, so this is really pretty close to the same
> functionality.
but beware of cross-schema dependencies! e.g., a DROP SCHEMA CASCADE of
schema X containg a table that has a column defined using a domain from
schema Y w
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> What are the total lengths of the log entries in which you see the
> failure? (The "length" here includes all the lines belonging to a
> single logical entry, eg, ERROR, DETAIL, HINT.)
It is very hard to tease these apart because now that I look at it
cesses are writing
at the same time uninterrupted.
Anything else I can do to diagnose?
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of George Pavlov
> Sent: Saturday, June 02, 2007 11:33 AM
> To: Tom Lane
> Cc: Ed L.; pgsql-general@
> From: Tom Lane
> "George Pavlov" <[EMAIL PROTECTED]> writes:
> >> From: Joshua D. Drake [mailto:[EMAIL PROTECTED]
> >> In those rare cases wouldn't it make more sense to just set
> >> enable_seqscan to off; run query; set enable_seqscan
> From: Joshua D. Drake [mailto:[EMAIL PROTECTED]
>
> In those rare cases wouldn't it make more sense to just set
> enable_seqscan to off; run query; set enable_seqscan to on;
1. these cases are not that rare (to me);
2. setting enable_seqscan (in JDBC, say) from the application makes the
whole
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> "George Pavlov" <[EMAIL PROTECTED]> writes:
> > I am curious what could make the PA query to ignore the
> index. What are
> > the specific stats that are being used to make this decision?
>
> you don
From: Tom Lane [mailto:[EMAIL PROTECTED]
> "George Pavlov" <[EMAIL PROTECTED]> writes:
> > ... Also redirect_stderr = on.
>
> Hm. Well, that's the bit that ought to get you into the PIPE_BUF
> exception. There's been some speculation that a change
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> "George Pavlov" <[EMAIL PROTECTED]> writes:
> > On 5/29/2007 10:19 AM, Ed L. wrote:
> >> FWIW, I've also been seeing this sort of query log corruption for
> >> as long as I can remember, 7.1 through
On 5/29/2007 10:19 AM, Ed L. wrote:
> On Wednesday 23 May 2007 1:04 pm, George Pavlov wrote:
> FWIW, I've also been seeing this sort of query log corruption for
> as long as I can remember, 7.1 through 8.2, HPUX (parisc, ia64),
> Linux on intel, amd...
Do you have any tricks for
I am trying to figure out how the distribution of data affects index
usage by the query because I am seeing some behavior that does not seem
optimal to my uneducated eye.
I am on PG 8.1.8. I have two tables foo and foo_detail, both have been
vacuum analyzed recently. Both have a property_id colum
seems hard to enforce integrity in your model. how are you going to
ensure that the user's city-state-country combo a valid one? (well, you
can, but it is a pain). ask yourself: can a city be in more than one
country? probably not (even if the name is the same it is not the same
city!). can a state
s).
Thanks,
George
> -Original Message-
> From: George Pavlov
> Sent: Wednesday, October 18, 2006 1:23 PM
> To: Tom Lane
> Cc: [EMAIL PROTECTED]
> Subject: RE: [GENERAL] query log corrupted-looking entries
>
> > the behavior. It'd be interesting to verify whe
no and no. a script looping through all tables (using
information_schema/pg_catalog info) is not that difficult to write
though.
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Robert James
> Sent: Thursday, May 17, 2007 4:23 PM
> To: pgsql-general@
> Well, that makes sense: if the shutdown took more than a
> minute then the
> "stop" script action would give up waiting, and then the
> "start" action
> would see the postmaster running and go away happy. (It's a bit odd
> that "service start" actions are supposed to treat "already running"
>
> > So it looks like the STOPPING of the service actually
> > succeeded, albeit
> > it took a while (more than the usual sessions open?). The
> > STARTING is
> > the one that actually failed (is that because the STOP was still in
> > process?). The question is why -- in a RESTART situation
> > w
where num_prods > num_open_issues + num_provisioned + num_canceled
if those columns are nullable (which they don't seem to be) you'd have
to convert the NULLs (i.e. coalesce(num_canceled,0) )
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Ketema
We have a nightly restart of one PG database. Today it failed and I
can't seem to understand why and how to prevent this in the future (nor
can I reproduce the problem).
We have a line in a shell script that calls "/etc/init.d/postgresql
restart". In the shell script's log from this invocation I h
a (possibly slightly more user-friendly) alternative to the catalog
table is pg_dump, e.g.:
pg_dump -d your_db_name -t your_table -s | grep 'CREATE INDEX'
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of
> Dmitry Koterov
> Sent: Tuesday, March 2
Currently "ALTER TABLE ... RENAME TO ..." results in all views that
refer to the table to be rewritten with the new table name. This is a
good thing in the general case, but there are also situations where it
is not (e.g. temporarily renaming tables for data reorg reasons). I
can't seem to find a c
FROM
ar
GROUP BY
customerid
) a
USING
(customerid)
WHERE
c.status = 'new';
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of George Pavlov
> Sent: Thursday, February 01, 2007 8:53 AM
> To: Demel, Jeff; pgs
try this approach:
SELECT
c.id,
c.firstname,
c.lastname,
a.latest_billdate
FROM
customers c
INNER JOIN -- or LEFT if you want the NULLs
(
SELECT
customer_id,
max(billdate) as latest_billdate
FROM
ar
) a
ON
c.customerid = a.customerid
WHERE
c.status = 'new';
First of all explicitly inserting IDs into your serial column sounds
like a bad idea (in the general case).
Unfortunately, I don't think inheritance can help you with this. Key
quote from the docs: "A serious limitation of the inheritance feature is
that indexes (including unique constraints) and
> For larger tables, you may have to resort to a
> union:
>
>select * from foo where name != 'Other' order by name
>union
>select * from foo where name = 'Other'
Alas, this suggestion is wrong on two counts: (a) UNION expects a single
ORDER BY that applies to the whole recordset and
> "George Pavlov" <[EMAIL PROTECTED]> writes:
> >> During crash recovery?
> > no crashes, just normal DB operation...
>
> Hmm ... what is in pg_multixact/members/ again?
Now there is only a file named 0010 the date on which changes about
every 4-5 mi
> During crash recovery?
no crashes, just normal DB operation...
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
I see other posts on this log message before, but not clear to me how/if
they apply to me. Opinions appreciated. I am on 8.1.3 on Linux. I have a
log entry like this:
2006-11-08 12:38:34 PST [3739]: [3-1] LOG: could not truncate directory
"pg_multixact/members": apparent wraparound
Nothing troub
several ways to do it, here's one:
check (coalesce(a,b,c) not null)
if you want one or the other to be present, but not both see this
thread:
http://archives.postgresql.org/pgsql-general/2006-09/msg00207.php
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED
> Hmm. If the messages are less than PIPE_BUF bytes long (4096 bytes on
> Linux) then the writes are supposed to be atomic. Can you
> check whether
> the interspersal cases involve messages whose total length (all lines)
> exceeds 4K?
Tom,
Some of them involve long messages (>4K), but there ar
> On Mon, Oct 16, 2006 at 01:16:34PM -0700, George Pavlov wrote:
> > Hmm, I am not sure I particularly like this behavior or the
> > "ignore it"
> > advice. Suppose someone makes a typo in his/her table
> > definition: meant
> > to create an int4
> Yep, the array type is represented internally by prefixings an
> underscore. It's mentioned somewhere in the docs, but you may as well
> ignore it.
Hmm, I am not sure I particularly like this behavior or the "ignore it"
advice. Suppose someone makes a typo in his/her table definition: meant
to c
Is there any special meaning to preceding a datatype (or at least some
datatypes) in a table or function definition by underscore that is a
synonym for an array? I can't see it documented anywhere. Below are some
examples. The other question is why "_int4" parses to int[], but "_int"
does not, etc.
> What PG version is this, on what operating system? Do you have
> redirect_stderr enabled?
Sorry, I should have included that:
PostgreSQL 8.1.3 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.4.4 20050721 (Red Hat 3.4.4-2)
And yes, redirect_stderr = on. I have no definitive way of repr
after an attempt at stress-testing my app i started seeing some
corrupted-looking entries in the postgresql query log. for example:
...
2006-10-10 21:33:24 PDT [31175]: [509-1] LOG: statem2006-10-10 21:33:24
PDT [31166]: [4206-1] LOG: duration: 0.197 ms
ent: EXECUTE [PREPARE: SELECTP.IS_D
> Look into pgfouine on pgFoundry.
> http://pgfoundry.org/projects/pgfouine/
thanks! definitely much better, but still not entirely believable, at
least on first try. e.g. i have a query with 4 conditions in the ORDER
BY. pgfouine reports show the query as having the last 3 of those
repeated 18 t
What do you all use for query log analysis for Postgres. I feel/hope
like there must be something that I am missing.
I have tried PQA (http://pqa.projects.postgresql.org/) and it is very
problematic, at least with the kind of application we have. Some of the
problems:
* not aware of prepared sta
do it?
--
George Pavlov
http://mynewplace.com
415.348.2010 desk
415.235.3180 mobile
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
I would like to analyze server stats offline, so I attempt to pg_dump my
production database and then pg_restore it into another database. In the
process all stats seem to be reset (they are not completely zeroed). So
in production I have a table with the following stats (from
pg_stat_all_tables as
> Wondering how to invoke a application like diff from plpgsql? Thanks!
And don't forget that you are working with a database. Most diff-ing
uses can probably be handled by constructs like EXCEPT and INTERSECT to
say nothing of OUTER JOINs. Also, IS DISTINCT FROM is your friend if you
want a compa
6 4:00 PM
> To: George Pavlov
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] assymetry updating a boolean (=FALSE
> faster than =TRUE)
>
> "George Pavlov" <[EMAIL PROTECTED]> writes:
> > Here is something that seems anomalous to me: when I s
Here is something that seems anomalous to me: when I set a boolean field
to FALSE performance is much better than when I set it to TRUE. Any
reason for FALSE to be favored over TRUE?
Some details:
vacuum analyze my_table;
update my_table set is_foo=FALSE where some_id = 47;
--142 rows affected, 8
Is there a way to export the output of an arbitrary SQL query to a
quoted CSV file?
It seems that the COPY command can do that for regular tables (COPY foo
TO STDOUT WITH CSV QUOTE AS '"') but not for a query result. I could
create temp tables in a script but it seems that there should be a
simpl
Is there any requirement that you have to use REPLACE? Another (possibly
better) way to do the same is by doing:
select translate(your_string,'aeiou','') from your_table;
In my experience that performs much better than regexp. I just ran it on
a table of about 100K random U.S addresses and TRA
Looking at the information_schema.columns view I have been wondering why
it only shows the column_default for columns in tables owned by the
current user? Makes things a bit misleading. I am thinking at least
superusers should be able to see that?
This is what I am talking about:
CREATE OR REPLA
> I'm sure that this has been asked before but I can't find any
> reference to it in google, and the search facility on
> postgresql.org is currently down.
http://groups.google.com/groups?q=group%3Apgsql.*
provides the same with a slight delay but arguably a better user
interface.
> I have a l
Not sure what the correct forum for pgxml/xml2 questions is. I was
wondering what is the definition of "valid" that the xml_valid(text)
function that is part of that module uses? It seems different from the
W3C definition of "valid" XML (is there an implicit DTD?) Maybe it is
more akin to "well-for
> Quotes tend to imply a text field.
I think you meant to say "quotes imply a non-null text field". And, yes,
I am quite aware of that. The point of the thread was to see if there is
any way of avoiding/overriding that assumption.
> Assuming
> you don't want to write a short Perl script to pre-
they have the same kind of page setup for pg Admin:
http://pgsql.navicat.com/PG_Admin/index.php
this one renders...
both pages seem to tell robots not to cache them, so can't view a cached
view on google.
---(end of broadcast)---
TIP 4: Have you
> > I need to load CSV files that have quotes in data fields
> > that I want to
> > map to NULLs in the destination table. So if I see
> > ...,"",... that needs
> > to be mapped to a NULL (in an INTEGER field in this
> > particular case).
> > Are there any COPY command options that can do that?
I need to load CSV files that have quotes in data fields that I want to
map to NULLs in the destination table. So if I see ...,"",... that needs
to be mapped to a NULL (in an INTEGER field in this particular case).
Are there any COPY command options that can do that? It seems that PgSQL
COPY expect
use this:
$ psql -Uyer_user -dyer_database -fyer_copy_script.sql <
yer_data_file.csv
where yer-copy-script.sql is:
-- -
drop table foo;
create table foo (
c01 varchar,
c02 varchar,
c03 varchar,
c04 varchar,
c05 varchar,
c06 varchar,
c07 varchar,
c08 varchar,
I have installed PG on Windows XP successfully several times, but now I
have one machine on which the install
fails with an error message: "Failed to set permissions on the installed
files. Please see the logfile at ..." . The log file in question does
not look very useful. It has lines of the type
64 matches
Mail list logo
