On Mon, Nov 6, 2017 at 12:35 PM, Tom Lane wrote:
> David Pacheco writes:
> > I ran into what appears to be a deadlock in the logging subsystem. It
> > looks like what happened was that the syslogger process exited because it
> > ran out of memory. But before the postm
;double precision" (which is a weird
> name in my opinion -- why can't it just be double) and not money?
>
The lack of quotes surrounding the value is significant. Money input
requires a string literal. Only (more or less) integer and double literal
values can be written without the single quotes.
David J.
t to accomplish.
If there is an addition to the system catalogs overview page of the docs
that describes the naming convention I think that would be swell.
David J.
On Mon, Nov 6, 2017 at 12:35 PM, Tom Lane wrote:
> David Pacheco writes:
> > ... that process appears to have exited due to a fatal error
> > (out of memory). (I know it exited because the process still exists in
> the
> > kernel -- it hasn't been reaped yet -- and
has for a
value and then figure out from there where that value is coming from.
David J.
Hello,
I ran into what appears to be a deadlock in the logging subsystem. It
looks like what happened was that the syslogger process exited because it
ran out of memory. But before the postmaster got a chance to handle the
SIGCLD to restart it, it handled a SIGUSR1 to start an autovacuum worker.
(), 'INSERT', ...
>
>
The second column of listings_cdc has a type of integer. The word INSERT
is not a valid value to store there.
David J.
On Wed, Nov 1, 2017 at 12:25 PM, Justin Pryzby wrote:
> On Wed, Nov 01, 2017 at 12:19:21PM -0700, David G. Johnston wrote:
> > On Wed, Nov 1, 2017 at 11:59 AM, Scott Marlowe
> > wrote:
> >
> > > So some of my output from an explain analyze here has a line that s
=0.110..0.111 rows=0 loops=1,010,844)
>
Not my strong suit but, I'm pretty sure that reads: "The index was queried
1M+ times and none of those inqueries resulted in a record being found".
IIUC I'd be wondering why some form of hash join wasn't used...
David J.
nect to the db as rakesh who is part of db_rw role.
This will not work because settings (GUCs) on a role are not inherited
by roles (or users) that are members of that role. This is a
characteristic of the roles system and not inherent to pgAudit.
--
-David
da...@pgmasters.net
--
Sent vi
specially vis-a-via both the from_item and grouping_element
specifications).
David J.
dequate but
not as clean. I'm consciously trying to write queries that don't require
application schemas in the search path: including the joyous
operator(altschema.@@) syntax in some situations. I suppose inference
could be considered in that situation as well.
David J.
hat fragment that is needed if one is going to develop a
data access and change management policy.
David J.
uld be changed in lieu of passing
options via the command line.
Also if conflicting, which one
> takes priority.
>
https://www.postgresql.org/docs/9.6/static/config-setting.html#AEN32498
David J.
log files.
>
>
> However, while about 40 of them have been deleted, 183 still exist. What
> did I do wrong?
>
>
Reading the old thread it sounds like it might require multiple vacuums to
affect complete removal.
David J.
h...
>
>
The unusual set of parentheses are so the parser interprets data_comp is
a column and not a table. Usually one write SELECT tbl.* FROM tbl so that
is the assumed meaning of "name".*
David J.
to
return the raw record data to bash and then use bash's loop facilities to
dynamically generate and execute the curl command.
A second option, that I've never tried, is returning the full string but
not within a backtick command, then using bash looping simply invoke the
string like a normal command.
David J.
ql/10/extension.
>
> How do I know beforehand where the dir path is ?
>
I think pg_config (
https://www.postgresql.org/docs/current/static/app-pgconfig.html ) is what
you are looking for.
David J.
On Thu, Oct 19, 2017 at 5:32 PM, John R Pierce wrote:
> On 10/19/2017 1:25 PM, Tomas Vondra wrote:
>
> Is it fine to create a subdir inside PGDATA and store our stuff
> there, or will PG freak out seeing a foreign object.
>
>
> PostgreSQL certainly does not check if there are unknown directories
ferences between using multiple from items and an explicit
cross join.
David J.
nt
with using psql.
Adding lots of new custom syntax to pure server-side parsed SQL is a
non-trivial undertaking whose need is reduced by the alternatives so
described (functions, DO block, PREPARE, psql).
David J.
Hi everyone,
i have question if is possible log count row of COPY command to
csv/syslog. I know that there are some limitations like triggers BEFORE
INSERT. Don't know if any others were pleased with this feature.
Have a nice day.
David
--
-
Ing.
benefits of running pre-release
versions in staging environments against stable business code please start
a new thread focused on the "process" and not the "people".
David J.
e closer to the first position you are contributing back
to the community and also the more quickly you can benefit from the new
features and enhancements each new release brings.
David J.
workloads that were not known or
available to the developers. Its a balancing act for most and falling on
the side of waiting for a few point releases before promoting to production
is, I suspect, common.
David J.
ead 3 hours, the type of data that has that field
> is timestamp without time zone,
>
> Please forgive my english I'm using translator.
>
A minimal SQL example of your problem would help.
David J.
ne know what is going on here ?
>
The "max(ts)" result indicates a time of midnight, the 6th, GMT
ts > '2017-10-06 00:01:01+01' equates to > '2017-10-05 23:01:01+00' of
which midnight, the 6th, GMT is indeed more recent
ts > '2017-10-06 00:01:01+00' is 12:01:01 on the 6th, GMT, of which
midnight GMC, the 6th is NOT more recent
David J.
ECT ... WHERE = :'varname';
https://www.postgresql.org/docs/10/static/app-psql.html
David J.
t; there seems odd too, wouldn't "membership" be more
appropriate?
https://www.postgresql.org/docs/10/static/sql-reassign-owned.html
David J.
only for objects that will be
created by yourself or by roles that you are a member of."
You use the "FOR " modifier if you want the "or by roles that
you are a member of" portion to be used.
David J.
n disk
> > > at the cost of lookup overhead for all queries?
> > > (I guess maybe it's like TOAST, but content-hashed and de-duped and not
> > > only for large objects?)
>
> On Fri, Oct 13, 2017, at 01:12 PM, David G. Johnston wrote:
> > Row-independen
aybe it's like TOAST, but content-hashed and de-duped and not
> only for large objects?)
>
Row-independence is baked into PostgreSQL pretty deeply...
I think an enum type is about as close are you are likely to get if you
don't wish to setup your own foreign-key relationships with surrogate keys.
David J.
that the planner was able to pull the
subquery (or view) up a level. When the planner is able to do this
it's much more flexible to the types of plans it can generate. It's
just that we don't ever pull up subqueries with DISTINCT ON, plus a
bunch of other reasons.
--
David Rowley
he WHERE to pass through ORDER BY.
>
> A special case can be allowed for WHERE to pass the ORDER BY if the column is
> part of DISTINCT ON.
Yeah, we do allow predicates to be pushed down in that case.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24
he existing one.
If the data is directly within the runtime container it will be
considerably more difficult - though pg_dump/pg_restore might prove easiest
to accomplish.
David J.
On 10 October 2017 at 12:44, Tom Lane wrote:
> David Rowley writes:
>> If the only reason that is_simple_subquery() rejects subqueries with
>> ORDER BY is due to wanting to keep the order by of a view, then
>> couldn't we make is_simple_subquery() a bit smarter a
On 10 October 2017 at 02:51, Tom Lane wrote:
> David Rowley writes:
>> It's pretty bad practice to have ORDER BY in views. I kinda wish we
>> didn't even allow it, but that ship sailed many years ago...
>
> I think it's actually disallowed by the SQL spec (al
On Mon, Oct 9, 2017 at 9:44 AM, Igal @ Lucee.org wrote:
> But I want to give that role permissions on future tables since I add new
> tables and drop/recreate current ones.
>
ALTER DEFAULT PRIVILEGES
https://www.postgresql.org/docs/9.6/static/sql-alterdefaultprivileges.html
David J.
ut the standard advice - don't roll-your-own backup solution -
investigate and choose one the many supported and maintained backup
solutions (most are open source) that are already available. This kind of
detail, then, should become unimportant to you.
David J.
mer_id) to
parameterise the nested loop, at least, it likely would, if you have
one.
It's pretty bad practice to have ORDER BY in views. I kinda wish we
didn't even allow it, but that ship sailed many years ago...
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL
on? Any progress in btree-support?
Not exactly what you're asking, but perhaps
https://commitfest.postgresql.org/14/1001/ could improve your
workload, or perhaps you could just manually rewrite the query.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development
]
https://www.postgresql.org/message-id/flat/CAKJS1f9FK_X_5HKcPcSeimy16Owe3EmPmmGsGWLcKkj_rW9s6A%40mail.gmail.com#cakjs1f9fk_x_5hkcpcseimy16owe3empmmgsgwlckkj_rw9...@mail.gmail.com
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training &a
.due_row_id
) c ON c.due_row_id = a.row_id;
SQL Server will probably be doing this rewrite.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To m
standard forces us to
do otherwise function invocation requires parentheses.
See 9.9.4 (
https://www.postgresql.org/docs/9.6/static/functions-datetime.html )
David J.
On Mon, Oct 2, 2017 at 7:09 AM, Guyren Howe wrote:
> I logged out and back and did SET ROLE and got the same resullt.
>
Are you logging in as "thing_accessor" or some role that is a member of
"thing_accessor"?
David J.
ma search path.
>
Settings associated with roles only take affect at session start/login.
SET ROLE does not cause them to be read/executed.
https://www.postgresql.org/docs/9.6/static/config-setting.html
"Values set with ALTER DATABASE and ALTER ROLE are applied only when
starting a fresh database session."
David J.
On Sat, Sep 30, 2017 at 10:57 AM, Alexander Kukushkin
wrote:
> Hi,
>
> Recently I've been investigating a strange behavior of one stored
> procedure.
>
Please provide the output of:
SELECT version();
David J.
nable to access the local program being referred to. The
server sees "FROM stdin" and psql feeds the results of the PROGRAM
invocation to the server over that pipe.
David J.
he pl/pgsql execution layer to trap "arbitrary SQL-layer
exceptions" is fairly expensive. Even if the user specifies specific
errors the error handling mechanism in pl/pgsql is code for generic
(arbitrary) errors being given to it.
David J.
ot so
> good.
>
There is a correlation between the decreased downtime that pg_upgrade
facilitates and its complexity.
If you cannot afford the 2 hours of downtime for dump/restore then likely
learning the pg_upgrade process would be the better choice for you.
David J.
doubting "plan caching" has anything to do with this; I
suspect its basically that there is high memory and runtime overhead to
deal with the possibilities of needing to convert a exception into a branch
instead of allowing it to be fatal.
David J.
ys
false" predicate and that simple of a query structure most of the other
stuff, including execution, is probably performed is seemingly zero time
but it still has to work through that step of the process - if nothing else
than to move through an if-branch to decide that nothing material needs to
be done.
David J.
r only option for table-to-table and
you are stuck with whatever locks the execution of the command needs to
take.
David J.
safe. Even if
it says it is there are potential gotchas. For example, the backup may
not be consistent if you are using multiple volumes.
In addition, data loss on restore will be greater if there is no WAL
archive to play forward from.
--
-David
da...@pgmasters.net
--
Sent via pgsql-genera
tence wouldn't hurt...?
The docs on configuring the server to speak to this overall flow and what
happens when invalid values are encountered (i.e., they are ignored)
https://www.postgresql.org/docs/current/static/config-setting.html#CONFIG-INCLUDES
David J.
diff --git a/doc/src/sgml/func.sg
t change the already published puzzles...
>
>
Serial (i.e. integer/bigint with an sequence generator). I would then add
another table that simply stores the "last manually reviewed id" as its
only record (or you could record manual reviews and take the max of that
field from the table).
David J.
this being a bug.
The attached fixes.
(CC'd -hackers since we're starting to discuss code changes. Further
discussion which includes -hackers should drop the general list)
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training &a
ld setup an FK as well but it wouldn't provide the needed
guarantee.
Nothing will stop an administrator, or a user with incorrectly configured
permissions, from bypassing all of that but properly configured application
roles will be unable to do so.
David J.
t is physically stored. The
documentation about the numeric type on the server is accurate, but
apparently the psql application takes some liberties as to what it chooses
to display. It's display rules for numeric don't seem to be user
documented or configurable.
David J,
on't recall if or where it is documented but it is intentional., as is
the documented fact that only one instance of the named column appears in
the output.
David J.
On Thu, Aug 31, 2017 at 1:04 PM, George Neuner wrote:
> Does anyone know a way to do this reliably?
>
The psql "\copy" meta-command should be capable of doing what you desire.
David J.
On Wednesday, August 30, 2017, wrote:
>
> Hi,
>
> is there a way to add a table create (and perhaps schema modify) timestamp
> to the system?
>
>
There is not. You may wish to search the archives for discussions as to
why previous requests for this feature have not resulted in patches.
David J.
y've done it already.
Given your rough description I'm not seeing why you wouldn't just have the
trigger write a record out to some kind of journal table and poll that
table for new records whenever you wish instead of depending upon
LISTEN/NOTIFY.
David J.
and pass the content of the notification
payload to it via stdin.
Given what we allow for "\copy" I don't see any technical or conceptual
problems with such a feature. Just needs someone to be its primary author.
David J.
On Wed, Aug 23, 2017 at 6:08 PM, Tom Lane wrote:
> "David G. Johnston" writes:
> > I'm wondering if there is anything technical preventing someone from
> making:
>
> > DROP TEMP TABLE tablename;
>
> There is no great need for that because you can get th
e invalid data, for a given type, valid.
Some hackish use of views, and triggers thereon, may provide an avenue to
gradual migration.
David J.
cts.
Its seems quite a few significant changes (including version numbering)
have happened to the JDBC project in the past year but you will need to go
directly to the project's site/GitHub to keep abreast of all of it.
David J.
blename is not schema-qualified and a table with the
same names exists in the search path it would be dropped.
Plus its symmetrical with CREATE [TEMP] TABLE.
David J.
prepared
statement. The placeholder values supplied to a prepared statement are
treated as literals.
You may also want turn on statement logging in the server.
David J.
x27;m not
fluent enough to provide examples. If you provide the question/problem you
are trying to resolve others will likely offer suggestions.
David J.
C4B8.7020200%40aklaver.com
David J.
ot;composite types".
A table IS a composite type (i.e., there is an implicit one of the same
name) for these purposes.
David J.
/static/auth-pg-hba-conf.html
David J.
separate table that you
update on insert using "UPDATE tbl SET col = col + 1 RETURNING col INTO
new_doc_num"
You could probably make it an unlogged table as well and you'd return from
the trigger function with new_doc_num if its non-null otherwise you'd
branch and re-create the record before returning the just queried maximum +
1.
David J.
that since* *"OWNER TO new_owner" is available in all
> other ALTER object statements, it is an omission and should be*
> *included for extenstions as well..*
>
As am I, but omission or not I don't recall that we've ever back-patched
new SQL grammar.
David J.
On Wed, Aug 9, 2017 at 11:30 AM, Melvin Davidson
wrote:
>
>
> On Wed, Aug 9, 2017 at 1:56 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane wrote:
>>
>>> Scott Marlowe writes:
>>> >
ted by the extension's script.
2>You must own the extension to use ALTER EXTENSION. The ADD/DROP forms
require ownership of the added/dropped object as well.
3>CREATE EXTENSION additionally records the identities of all the created
objects, so that they can be dropped again if DROP EXTENSION is issued.
David J.
not quite.
I eventually saw, in one of the three descriptions for "tuples only" where
the caption is explicitly noted as being hidden in this mode. In my
proposed feature the caption, like the table it is attached to, would be
visible only conditionally.
David J.
The names are descriptive, not suggestions...
David J.
tgreSQL's problem you should be able to setup statement logging
on the server and see what is happening at the SQL level.
David J.
On Wed, Aug 2, 2017 at 4:50 PM, Peter Koukoulis
wrote:
> david, thanks for the help.
>
> Would this be the equivalent, for the statement in your email, for table
> TEST1 (x integer, y varchar(20)):
>
> ft_node=# SELECT md5(string_agg(vals::text, ''))
> ft_node-# fr
B783172CA371DA04AD5754
>
>
> Can I do something similar in PostgreSQL ?
>
>
Similar.
SELECT md5(string_agg(vals::text, ''))
FROM ( VALUES (1, 2), (2, 3) ) vals (x, y)
David J.
On Wed, Aug 2, 2017 at 10:10 AM, Edmundo Robles
wrote:
> I imagine pg_restore can execute the instructions on dump but don't
> write on disk. just like David said: "tell me what is going to happen
> but don't actually do it"
>
You may wish to respond to
join" two DDL commands by using a Common Table Expression (CTE)
(i.e., WITH / SELECT). You would need to make it so the UPDATE happens
first and if there are no results the INSERT simply becomes a no-op.
David J.
arger/more disks.
Personally, I'd probably setup a dedicated "test restore" cluster with lots
of HD and put stuff like "fsync=off" into its postgresql.conf.
I could see having a "--make-tables-unlogged" option that would convert,
on-the-fly, all CREATE TABLE commands to "CREATE UNLOGGED TABLE" commands.
David J.
On Thursday, July 27, 2017, David G. Johnston
wrote:
> On Thursday, July 27, 2017, Vincenzo Romano > wrote:
>>
>> The main difference is that with RETURNS SETOF RECORD I still get the
>> "usual"(tm) function argument list in the usual place: between two
>>
t I still don't get the point for not having it for a single column.
>
>
Docs say: " When there are OUT or INOUT parameters, the RETURNS clause can
be omitted. ". Sounds like you should you do just that.
David J.
I'm seeing that the expected number of input rows doesn't factor
into the innerbucketsize computation directly (possibly excepting a scaling
factor adjustment).
I can understand better, now, why this seemingly perfect example of a
semi-join query gets executed with an extra distinct/grouping node.
David J.
On Mon, Jul 24, 2017 at 7:58 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane wrote:
>
>>
>> The cost to form the inner hash is basically negligible whether it's
>> de-duped or not, but if it's not (k
etail would be welcome. Not sure what to grep for - "Hash Semi Join"
only turns up a couple of expected output results...
Thx.
David J.
t I believe is required for the semi-join algorithm to
be considered.
David J.
ELECT *
FROM tbl
WHERE EXISTS (SELECT 1 FROM tbl2 WHERE tbl.id = tbl2.id)
The main difference between IN and EXISTS is NULL semantics.
David J.
aningful and so the convenience of writing (IN (...)) is worth taking.
David J.
On Sunday, July 23, 2017, Igor Korot wrote:
> Hi, David,
>
> On Sun, Jul 23, 2017 at 5:07 PM, David G. Johnston
> > wrote:
> > On Sunday, July 23, 2017, Igor Korot >
> wrote:
> >>
> >> Is "IF" operator not supported by PostgreSQL
>
>
On Sunday, July 23, 2017, Igor Korot wrote:
>
> Is "IF" operator not supported by PostgreSQL
>
IF is pl/pgsql, not SQL.
David J.
s one action
- parse. The subsequent action is "execute" the contents of the SELECT
that is now in the active state of the session.
If the above is correct the docs could be more clear since I'd agree that
your interpretation of the field seems more logical - though knowing
initial state is quite useful in its own right.
David J.
have this kind of
discussion. Since it is always going to be "0" we simply omit printing it.
David J.
On Thursday, July 20, 2017, David G. Johnston
wrote:
> On Thursday, July 20, 2017, Igor Korot > wrote:
>
>> Hi, David,
>>
>> On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston
>> wrote:
>> > On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot wrote:
>
On Thursday, July 20, 2017, Igor Korot wrote:
> Hi, David,
>
> On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston
> > wrote:
> > On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot > wrote:
>
> >> Is there a query or a libpg function which can return the ve
should be the result (don't have that version installed to
copy-paste)
90506 is the version I have at my fingertips.
David J.
1 - 100 of 4464 matches
Mail list logo
