On Tue, Sep 27, 2016 at 2:13 PM, raf wrote:
> So, my qestion is, is it possible that "pg_ctl status" could be
> removing postgres's semaphores and can I stop it? It seems
> extremely unlikely. So, if it isn't, what else could it be?
> Systemd perhaps? It's been known to kill screen/tmux/nohup
> pr
Hello,
>From time to time we receive following event from application (Adobe
Campaign - former Neolane):
PostgreSQL error: lost synchronization with server: got message type "Z",
length 0\n (iRc=-2006)
Last time underlying sql statement was an update.
At the backend side there is
We also tried to achieve incremental refresh of materialized view and our
solution doesn't solve all of the use cases.
Players:
1) WAL
2) Logical decoding
3) replication slots
4) custom background worker
Two kinds of approaches :
1. Deferred refresh (oracle type of creating log table for each bas
Hi,
debian-8 (stable), postgres-9.4.9
I've just started running "/etc/init.d/postgresql-9.4 status"
every minute via cron and it seems to be having a very bad effect
on the server ["So stop doing it!" heard from the peanut gallery].
I noticed an error message like:
FATAL: the database system
2016-09-27 16:22 GMT+13:00 Patrick B :
> Hi guys,
>
> I've got 2k rows in a table:
>
>> CREATE TABLE
>> public.not_monthly
>> (
>> id BIGINT DEFAULT "nextval"('"id_seq"'::"regclass") NOT NULL,
>> clientid BIGINT,
>> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
Hi guys,
I've got 2k rows in a table:
> CREATE TABLE
> public.not_monthly
> (
> id BIGINT DEFAULT "nextval"('"id_seq"'::"regclass") NOT NULL,
> clientid BIGINT,
> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
> name_last CHARACTER VARYING(80) DEFAULT ''::
On Mon, Sep 26, 2016 at 7:49 PM, hariprasath nallasamy
wrote:
>We are using replication slot for capturing some change sets to
> update dependent tables.
>Will there be inconsistency if the master fails and the standby takes
> the role of master.?
Replication slot creation is not
On Tue, Sep 27, 2016 at 9:26 AM, Francisco Reyes wrote:
> The only times archive command failed I believe is because the volume where
> pg_xlog is ran out of space.
> FATAL: archive command was terminated by signal 3: Quit
>
> There are 2 of those today. So that does not seem to be the cause.
Wh
On 09/26/2016 08:08 PM, Andreas Kretschmer wrote:
archive_command failed? If that happens the wal's will not deleted,
you should see error-messages in the log.
The only times archive command failed I believe is because the volume
where pg_xlog is ran out of space.
FATAL: archive command wa
On 27 September 2016 01:52:26 CEST, Francisco Reyes wrote:
>Any ideas why pg_xlog is going so high?
archive_command failed? If that happens the wal's will not deleted, you should
see error-messages in the log.
--
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.
Postgresql 9.3
checkpoint_segments=6
wal_keep_segments=300
Machine is master for 2, asynchronous, slaves.
pg_xlog in /var/lib/postgresql/9.3/main/pg_xlog
NFS mount with WAL archiving in /opt/backups/walarchives/HostName
During a load of a file, using copy, the pg_xlog grew to almost 120GB
Please include the list in all replies.
On Mon, Sep 26, 2016 at 4:14 PM, Jonathan Vanasco wrote:
>
> On Sep 26, 2016, at 5:04 PM, David G. Johnston wrote:
>
> On Mon, Sep 26, 2016 at 1:44 PM, Jonathan Vanasco
> wrote:
>
>> The documentation doesn't have any examples for SELECT for the bitwis
On Mon, Sep 26, 2016 at 1:44 PM, Jonathan Vanasco wrote:
> The documentation doesn't have any examples for SELECT for the bitwise
> operators,
Um...
https://www.postgresql.org/docs/9.5/static/functions-bitstring.html
SELECT B'111'::varbit & B'101'::varbit = B'101'::varbit
SELECT 10::bit(8
We've been storing some "enumerated"/"set" data in postgresql as INT or BIT(32)
for several years for some flags/toggles on records.
This was preferable for storage to the ENUM type (or multiple columns), as we
often changed the number of enumerated options or their labels -- and computing
ev
On Mon, Sep 26, 2016 at 3:16 PM, Adam Brusselback
wrote:
> Well I feel like I've learned a ton already reading through the links you
> provided earlier and that example above.
Yeah, I know that example can really help show what will happen
"under the covers", and make it more concrete. The theo
On Mon, Sep 26, 2016 at 3:21 PM, Kevin Grittner wrote:
> On Mon, Sep 26, 2016 at 2:04 PM, Rakesh Kumar
> wrote:
>
> > Does PG have a concept of MV log, from where it can detect the
> > delta changes and apply incremental changes quickly.
>
> That is what I am trying to work toward with the patc
Tom van Tilburg writes:
> I'm often using the WHERE clause random() > 0.5 to pick a random subset of
> my data. Now I noticed that when using a set-returning function in a
> sub-query, I either get the whole set or none (meaning that the WHERE
> random() > 0.5 clause is interpreted *before* the se
On Mon, Sep 26, 2016 at 2:04 PM, Rakesh Kumar
wrote:
> Does PG have a concept of MV log, from where it can detect the
> delta changes and apply incremental changes quickly.
That is what I am trying to work toward with the patch I cited in
an earlier post. Once some variation of that is in, the
Sorry, I've just realized you did that already.
And the WITH cte AS part is optional in this case...
Thank you
On Mon, Sep 26, 2016 at 9:12 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:
> Thank you Vik and others -
>
> On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing wrote:
>
>> On 09/
Thank you Vik and others -
On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing wrote:
> On 09/26/2016 08:22 PM, Alexander Farber wrote:
> >
> > CREATE OR REPLACE FUNCTION words_get_chat(
> > in_uid integer,
> > in_gid integer,
> > in_msg varchar
> >
> Of course 9.5 is the current release so the answer is Yes, since 9.5
https://www.postgresql.org/docs/current/static/sql-creatematerializedview.html
What am I missing. I don't see any support for incremental refresh.
Just in case we are not speaking the same thing: When a MV is created for the
Hi,
You could run 2 queries separatly and asynchrouneously
1) the limit 10
2) the count
While the limit 10 query would be showned instanteneously, the web table
would way for the count to build the pagination
Le lun. 26 sept. 2016 à 20:59, Leonardo M. Ramé a
écrit :
> Hi, I'm using a query t
On 09/23/2016 10:26 PM, PHANIKUMAR G wrote:
hi owners/admins of postgresql,
my name is phani kumar and I would like to be member of postgres
forums/groups/communities. I have already sent mail regarding my
concern, looks my request overlooked and i did not get any response.
Second time I am app
On Mon, Sep 26, 2016 at 1:22 PM, Melvin Davidson wrote:
> On Mon, Sep 26, 2016 at 1:56 PM, Adam Brusselback
> wrote:
>>
>> I am working on a plan to implement incrementally refreshed
>> materialized "views" with the existing functionality in
>> Postgres.
>>
>> Below is the plan for doing that:
On Mon, Sep 26, 2016 at 2:35 PM, Rob Sargent wrote:
> Of course 9.5 is the current release so the answer is Yes, since 9.5
>
> It seems like there is some confusion about what we're talking about. I am
talking about incremental updates to a sort of "fake" materialized view
(implemented as a table
Hi, I'm using a query to fill a paginated table. The task involves
filtering, sorting, limit, offset and count of all rows (to determine
the number of pages).
My current query is this:
select count(*) over() as totalrows,
case when (d.filepath is not null) then '1' else '0' end as HasDocumen
I require eagerly refreshed materialized views for my use case, which is
something Postgres does not currently support. I need my updates to a
table the view refers to visible within the same transaction, and often it
is a single change to one row which will only effect a single row in the
view.
On 09/26/2016 08:22 PM, Alexander Farber wrote:
> Good evening!
>
> For a 2-player game I am trying to create a custom SQL function, which
> stores a new message (if not empty) into words_chat table and then
> return all messages from that table for a given game:
>
> CREATE OR REPLACE FUNCTION wo
Of course 9.5 is the current release so the answer is Yes, since 9.5
On 09/26/2016 12:29 PM, Rakesh Kumar wrote:
*Does PG support INCREMENTAL MV ? Looks like not (until 9.5)*
Does PG support INCREMENTAL MV ? Looks like not (until 9.5)
On Mon, Sep 26, 2016 at 08:22:11PM +0200, Alexander Farber wrote:
> ERROR: syntax error at or near "IF"
> LINE 11: IF LENGTH(TRIM(in_msg)) > 0 AND
> ^
of course it doesn't like it, because sql doesn't have "if" command.
If you want to use such syntax, you have to use plp
Hi
2016-09-26 20:22 GMT+02:00 Alexander Farber :
> Good evening!
>
> For a 2-player game I am trying to create a custom SQL function, which
> stores a new message (if not empty) into words_chat table and then return
> all messages from that table for a given game:
>
> CREATE OR REPLACE FUNCTION
On Mon, Sep 26, 2016 at 1:56 PM, Adam Brusselback wrote:
> Hello all,
> I am working on a plan to implement incrementally refreshed materialized
> "views" with the existing functionality in Postgres.
>
> Below is the plan for doing that:
>
> Trigger based eagerly updated materialized tables for P
Good evening!
For a 2-player game I am trying to create a custom SQL function, which
stores a new message (if not empty) into words_chat table and then return
all messages from that table for a given game:
CREATE OR REPLACE FUNCTION words_get_chat(
in_uid integer,
Hello all,
I am working on a plan to implement incrementally refreshed materialized
"views" with the existing functionality in Postgres.
Below is the plan for doing that:
Trigger based eagerly updated materialized tables for Postgres 9.5
>
>
>
> High level plan:
>
> Have a view definition stored
hi owners/admins of postgresql,
my name is phani kumar and I would like to be member of postgres
forums/groups/communities. I have already sent mail regarding my concern,
looks my request overlooked and i did not get any response. Second time I
am approaching you, please add my mail id phanikumar
Hi List,
Note beforehand: this question is a result of a stack-exchange that can be
seen here:
http://stackoverflow.com/questions/39624241/inconsistent-behaviour-of-set-returning-functions-in-sub-query-with-random
I'm often using the WHERE clause random() > 0.5 to pick a random subset of
my data.
> > Can you elaborate? Why would anyone create a text column to store
> customer name or product name which can very well be in varchar(50)
> type of cols.
>
> You sound like you think that varchar(50) is somehow cheaper than text.
> That's backwards (at least in PG, other DBMSes may be differe
On Mon, Sep 26, 2016 at 9:18 AM, 邓彪 wrote:
> we have to do dml in temp table,the CTE is not fit
>
>
Moving this to -general only...
Please direct all replies to the list.
You are asking for help but not providing any context for what your
requirements are. You are not likely to get good hel
2016-09-26 17:39 GMT+02:00 dby...@163.com :
> test:
> create type h3 as (id int,name char(10));
>
> CREATE or replace FUNCTION proc17()
> RETURNS SETOF h3 AS $$
> DECLARE
> v_rec h3;
> BEGIN
> create temp table abc(id int,name varchar) on commit drop;
> insert into abc select 1,'lw';
> inser
Its considered bad form to post to multiple lists. Please pick the most
relevant one - in this case I'd suggest -general.
On Mon, Sep 26, 2016 at 8:39 AM, dby...@163.com wrote:
>
> Array is not convenient to use in function, whether
> there are other methods can be replaced temp table in functi
test:
create type h3 as (id int,name char(10));
CREATE or replace FUNCTION proc17()
RETURNS SETOF h3 AS $$
DECLARE
v_rec h3;
BEGIN
create temp table abc(id int,name varchar) on commit drop;
insert into abc select 1,'lw';
insert into abc select 2,'lw2';
for v_rec in
select * from abc loop
On Monday, September 26, 2016 9:44 AM, Tom Lane wrote:
>> Paul Jones writes:
>> For a freshly pg_restore'd 9.2 database, would VACUUM ANALYZE update
>> statistics any better than just an ANALYZE?
>
> VACUUM would have caused the page-all-visible flags to get set for all
> pages of unchang
Rob Sargent writes:
> On 09/26/2016 08:14 AM, Adrian Klaver wrote:
>> https://www.postgresql.org/docs/9.5/static/datatype-character.html
>> ".. If character varying is used without length specifier, the type
>> accepts strings of any size. The latter is a PostgreSQL extension."
> Does that trick
On 09/26/2016 07:38 AM, Rob Sargent wrote:
On 09/26/2016 08:14 AM, Adrian Klaver wrote:
On 09/26/2016 06:54 AM, Thomas Kellerer wrote:
Rakesh Kumar schrieb am 26.09.2016 um 15:08:
You sound like you think that varchar(50) is somehow cheaper than
text.
The biggest impediment to text cols in
On 09/26/2016 07:38 AM, Rob Sargent wrote:
On 09/26/2016 08:14 AM, Adrian Klaver wrote:
On 09/26/2016 06:54 AM, Thomas Kellerer wrote:
Rakesh Kumar schrieb am 26.09.2016 um 15:08:
You sound like you think that varchar(50) is somehow cheaper than
text.
The biggest impediment to text cols in
On 09/26/2016 08:14 AM, Adrian Klaver wrote:
On 09/26/2016 06:54 AM, Thomas Kellerer wrote:
Rakesh Kumar schrieb am 26.09.2016 um 15:08:
You sound like you think that varchar(50) is somehow cheaper than
text.
The biggest impediment to text cols in other RDBMS is no index
allowed.
If PG ha
On 09/26/2016 06:54 AM, Thomas Kellerer wrote:
Rakesh Kumar schrieb am 26.09.2016 um 15:08:
You sound like you think that varchar(50) is somehow cheaper than text.
The biggest impediment to text cols in other RDBMS is no index allowed.
If PG has an elegant solution to that, then yes I see the
Rakesh Kumar schrieb am 26.09.2016 um 15:08:
>>You sound like you think that varchar(50) is somehow cheaper than text.
>
> The biggest impediment to text cols in other RDBMS is no index allowed.
> If PG has an elegant solution to that, then yes I see the point made by the
> original poster.
Don
Paul Jones writes:
> For a freshly pg_restore'd 9.2 database, would VACUUM ANALYZE update
> statistics any better than just an ANALYZE?
Not as far as the statistics kept in pg_stat go.
> After a restore, we ran a bunch of ANALYZEs on each table individually
> using GNU 'parallel' (for speed). M
For a freshly pg_restore'd 9.2 database, would VACUUM ANALYZE update
statistics any better than just an ANALYZE?
After a restore, we ran a bunch of ANALYZEs on each table individually
using GNU 'parallel' (for speed). Many of these tables are child tables
in a partition. Following the ANALYZEs,
TEXT is a native type in PostgreSQL, and is highly optimized behind the scenes
to be as fast and efficient as possible in both the storage and retrieval of
the data.
Regarding user input validation, it is almost always better to let the
customer-facing app do the validation instead of relying u
>You sound like you think that varchar(50) is somehow cheaper than text.
The biggest impediment to text cols in other RDBMS is no index allowed.
If PG has an elegant solution to that, then yes I see the point made by the
original poster.
Rakesh Kumar writes:
> Can you elaborate? Why would anyone create a text column to store customer
> name or product name which can very well be in varchar(50) type of cols.
You sound like you think that varchar(50) is somehow cheaper than text.
That's backwards (at least in PG, other DBMSes may
>I have done some research after converting my database from MySQL 5.6 to
>PostgreSQL 9.6 (the best move I have ever made),
>and the consensus I found can be summed up as:
>1. Never, neve, never use VARCHAR or even CHAR
>2. Always always, always use TEXT
>Unless, that is, you have some kind of
I have done some research after converting my database from MySQL 5.6 to
PostgreSQL 9.6 (the best move I have ever made), and the consensus I found can
be summed up as:
1. Never, neve, never use VARCHAR or even CHAR
2. Always always, always use TEXT
Unless, that is, you have some kind of edge c
On 2016-09-26 1:15 AM, Gavin Flower wrote:
On 26/09/16 17:58, Patrick B wrote:
Hi guys,
I've got this domain:
CREATE DOMAIN public.a_city
AS character varying(80)
COLLATE pg_catalog."default";
And I need to increase the type from character varying(80) to
character varying(2
Hi all
We are using replication slot for capturing some change sets to
update dependent tables.
Will there be inconsistency if the master fails and the standby
takes the role of master.?
cheers
-harry
On Mon, Sep 26, 2016 at 4:01 PM, PHANIKUMAR G wrote:
> We have registered postgres(version 9.3.4) as windows service on windows
> 2008 R2 and registration got succeeded.
Just a random thought: do you have ASLR enabled in this build (from
where is this build)? You can check that easily using dumpb
hi,
We are able to start the postgres from command line without any issue,
if postgres is registered as windows service and tried to start it, we are
facing an issue .
*Problem:*
We have registered postgres(version 9.3.4) as windows service on windows
2008 R2 and registration got succeeded.
60 matches
Mail list logo
