Re: Old tsearch functions
On 30/01/2019 18:08, Tom Lane wrote: Note that if you had those functions laying around ever since 8.3, they're probably just "loose" and not wrapped into an extension at all. You could fix that in a 9.5 database by running create extension tsearch2 from unpackaged; which should be enough to collect the relevant objects into an extension. At that point you could try doing "drop extension tsearch2". Likely it'll fail due to dependencies on the extension objects, but at least the error message will give you an idea of what you need to fix before you can drop it. In any case, this certainly beats trying to manually identify and drop the obsolete types and functions. You will need to do this in 9.5, or at the latest 9.6, because we dropped support for that extension in v10. regards, tom lane Thanks Tom, unfortunately running create extension tsearch2 from unpackaged; caused the following error: ERROR: operator family "gist_tsvector_ops" does not exist for access method "gist" So I think I will have to create a script to delete the functions etc individually unless someone has another idea. For the tables that contain tsvector columns, is it OK to just run the following, or will i need to rebuild the associated index? alter column ALTER TABLE public.mytable ALTER COLUMN fts TYPE tsvector ; The current type is public.tsvector; Thanks.
problem
GoodMornig, I can’t install postgres on my pc because i have Always this error: “ Problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed.” I looked for a guide that can help me to solve this problem but it was all a big fail. Itried to create a new user on my pc to create postgres server but nothing. I tried to follow some guides on web/YouTube but nothing. Can you please help me to solve this problem? I must need to solve this because in my university they use postgres and i must need this to complete the exam and the project on database. Thank for patience. Have a good day, mirco Inviato da Posta per Windows 10
Re: problem
On 1/31/19 7:10 AM, Mirco Gallazzi wrote: GoodMornig, I can’t install postgres on my pc because i have Always this error: “ Problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed.” I looked for a guide that can help me to solve this problem but it was all a big fail. Itried to create a new user on my pc to create postgres server but nothing. I tried to follow some guides on web/YouTube but nothing. Can you please help me to solve this problem? I must need to solve this because in my university they use postgres and i must need this to complete the exam and the project on database. Thank for patience. Have a good day, mirco What version of Postgres are you trying to install, and onto what OS? (I presume Windows 10, but we must be sure.) -- Angular momentum makes the world go 'round.
Initial load from standby in logical replication.
Hi All, We want to setup a logical replica with initial load from physical standby. (all pg10.5) Basically what we tested is: 1.on the master: create publication and logical slot. 2.on the standby: pause the stanby. (lsn from 1. is already replayed). 3.on the standby: get last replayed lsn, pg_last_wal_replay_lsn(). 4.export/import the data into the logical replica. 5.on the logical replica: create subscription with pg_replication_origin_advance'd to the lsn from 3. 6.on the logical replica: enable subscription. On a test setup with some bulk inserts results are encouraging. Still we're not that confident. Does the procedure looks okay? What if standby is paused at COMMIT lsn? Thanks! Radoslav Nedyalkov
Re: Initial load from standby in logical replication.
On 31/1/19 3:55 μ.μ., Radoslav Nedyalkov wrote: Hi All, We want to setup a logical replica with initial load from physical standby. (all pg10.5) Basically what we tested is: 1.on the master: create publication and logical slot. 2.on the standby: pause the stanby. (lsn from 1. is already replayed). 3.on the standby: get last replayed lsn, pg_last_wal_replay_lsn(). 4.export/import the data into the logical replica. 5.on the logical replica: create subscription with pg_replication_origin_advance'd to the lsn from 3. 6.on the logical replica: enable subscription. On a test setup with some bulk inserts results are encouraging. Still we're not that confident. Does the procedure looks okay? What if standby is paused at COMMIT lsn? Who is gonna consume data from the primary's replication slot? If there is no subscriber connected and reading data, then the primary will think that the subscriber is down and will accumulate WALs till some replication client is connected to it (which will not happen since the subscriber points to the standby). Thanks! Radoslav Nedyalkov -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: Initial load from standby in logical replication.
On Thu, Jan 31, 2019 at 4:09 PM Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > On 31/1/19 3:55 μ.μ., Radoslav Nedyalkov wrote: > > Hi All, > > We want to setup a logical replica with initial load from physical > standby. (all pg10.5) > > Basically what we tested is: > > 1.on the master: create publication and logical slot. > > 2.on the standby: pause the stanby. (lsn from 1. is already replayed). > > 3.on the standby: get last replayed lsn, pg_last_wal_replay_lsn(). > > 4.export/import the data into the logical replica. > > 5.on the logical replica: create subscription > with pg_replication_origin_advance'd to the lsn from 3. > > 6.on the logical replica: enable subscription. > > > > On a test setup with some bulk inserts results are encouraging. > > Still we're not that confident. Does the procedure looks okay? > > What if standby is paused at COMMIT lsn? > Who is gonna consume data from the primary's replication slot? If there is > no subscriber connected and reading data, then the primary will think that > the subscriber is down and will accumulate WALs > till some replication client is connected to it (which will not happen > since the subscriber points to the standby). > Yes. that's clear. Primary will accumulate WALs until logical replica connects to it. The point is to offload the primary from massive initial copying. > > Thanks! > > Radoslav Nedyalkov > > > -- > Achilleas Mantzios > IT DEV Lead > IT DEPT > Dynacom Tankers Mgmt > > >
Re: Old tsearch functions
Howard News writes: > On 30/01/2019 18:08, Tom Lane wrote: >> Note that if you had those functions laying around ever since 8.3, >> they're probably just "loose" and not wrapped into an extension at all. > unfortunately running > create extension tsearch2 from unpackaged; > caused the following error: > ERROR: operator family "gist_tsvector_ops" does not exist for access > method "gist" That's odd, the tsearch2 extension has certainly been stagnant since 8.3. I wonder if the set of tsearch2 objects you have is even older than that. > So I think I will have to create a script to delete the functions etc > individually unless someone has another idea. I'd try trimming down the tsearch2--unpackaged--1.0.sql script until it succeeds. (Don't assume that you've got the exact same set of objects in every DB, either ...) > For the tables that contain tsvector columns, is it OK to just run the > following, or will i need to rebuild the associated index? > ALTER TABLE public.mytable > ALTER COLUMN fts TYPE tsvector ; The ALTER COLUMN will take care of rebuilding indexes, but just for certainty I'd suggest spelling that "TYPE pg_catalog.tsvector". regards, tom lane
Re: Old tsearch functions
On 31/01/2019 14:58, Tom Lane wrote:
Howard News writes:
On 30/01/2019 18:08, Tom Lane wrote:
Note that if you had those functions laying around ever since 8.3,
they're probably just "loose" and not wrapped into an extension at all.
unfortunately running
create extension tsearch2 from unpackaged;
caused the following error:
ERROR: operator family "gist_tsvector_ops" does not exist for access
method "gist"
That's odd, the tsearch2 extension has certainly been stagnant since
8.3. I wonder if the set of tsearch2 objects you have is even older
than that.
So I think I will have to create a script to delete the functions etc
individually unless someone has another idea.
I'd try trimming down the tsearch2--unpackaged--1.0.sql script until
it succeeds. (Don't assume that you've got the exact same set of
objects in every DB, either ...)
For the tables that contain tsvector columns, is it OK to just run the
following, or will i need to rebuild the associated index?
ALTER TABLE public.mytable
ALTER COLUMN fts TYPE tsvector ;
The ALTER COLUMN will take care of rebuilding indexes, but just for
certainty I'd suggest spelling that "TYPE pg_catalog.tsvector".
regards, tom lane
Thanks again Tom.
You may be correct about how old the version of tsearch was. I have not
seen the tsearch2--unpackaged--1.0.sql script yet, but the following
seems to cope ok. It works on both the 9.5 version and the 11.1 version.
[CODE]
begin;
-- Repeat the line below for each table with public.tsvector column:
alter table if exists mytable_with_fts business alter column fts type
pg_catalog.tsvector;
drop domain if exists public.tsvector;
drop domain if exists public.tsquery;
drop domain if exists public.gtsvector;
drop domain if exists public.gtsq;
-- This is how I created a list of functions in the public namespace
-- SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname
-- || '(' || oidvectortypes(proargtypes) || ');'
-- FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace =
ns.oid)
-- WHERE ns.nspname = 'public' order by proname;
drop function if exists public._get_parser_from_curcfg();
drop function if exists public.concat(tsvector, tsvector);
drop function if exists public.headline(text, tsquery);
drop function if exists public.headline(oid, text, tsquery);
drop function if exists public.headline(text, tsquery, text);
drop function if exists public.headline(oid, text, tsquery, text);
drop function if exists public.length(tsvector);
drop function if exists public.lexize(oid, text);
drop function if exists public.numnode(tsquery);
drop function if exists public.parse(oid, text);
drop function if exists public.parse(oid, text);
drop function if exists public.parse(text);
drop function if exists public.parse(text,text);
drop function if exists public.plainto_tsquery(text);
drop function if exists public.plainto_tsquery(oid, text);
drop function if exists public.plpgsql_call_handler();
drop function if exists public.plpgsql_validator(oid);
drop function if exists public.querytree(tsquery);
drop function if exists public.rank(tsvector, tsquery);
drop function if exists public.rank(real[], tsvector, tsquery);
drop function if exists public.rank(tsvector, tsquery, integer);
drop function if exists public.rank(real[], tsvector, tsquery, integer);
drop function if exists public.rank_cd(tsvector, tsquery);
drop function if exists public.rank_cd(real[], tsvector, tsquery);
drop function if exists public.rank_cd(tsvector, tsquery, integer);
drop function if exists public.rank_cd(real[], tsvector, tsquery, integer);
drop function if exists public.rewrite(tsquery, text);
drop function if exists public.rewrite(tsquery, tsquery, tsquery);
drop function if exists public.setweight(tsvector, "char");
drop function if exists public.show_curcfg();
drop function if exists public.stat(text);
drop function if exists public.stat(text, text);
drop function if exists public.strip(tsvector);
drop function if exists public.to_tsquery(text);
drop function if exists public.to_tsquery(oid, text);
drop function if exists public.to_tsvector(text);
drop function if exists public.to_tsvector(oid, text);
drop function if exists public.token_type(integer);
drop function if exists public.token_type(text);
drop function if exists public.token_type();
drop function if exists public.ts_debug(text);
drop function if exists public.tsq_mcontained(tsquery, tsquery);
drop function if exists public.tsq_mcontains(tsquery, tsquery);
drop function if exists public.tsquery_and(tsquery, tsquery);
drop function if exists public.tsquery_not(tsquery);
drop function if exists public.tsquery_or(tsquery, tsquery);
drop type if exists public.statinfo;
drop type if exists public.tokenout;
drop type if exists public.tokentype;
drop type if exists public.tsdebug;
commit;
[/CODE]
oracle_fwd - is it safe or not?
Hi everyone, I have some question considering about oracle_fdw and safety of use it in production environment. Problem we would like to resolve using this extension is to move data from five Oracle tables to Postgres database. To avoid situation that we have to trasfer data using sftp/ssh or another file based approach we decided to install oracle_fdw. Our administrator have some doubt about this extension. He asked us, are we sure that this extension safe enough and didn't make any kernel panic sytuation beacuse as all another extension, this one can operate on system kernel level. Therefore if it's possible can You tell me more about safety this extension and release procedure and community of this module? -- Best Regards Piotr Włodarczyk
Re: oracle_fwd - is it safe or not?
On Thu, Jan 31, 2019 at 05:37:00PM +0100, Piotr Włodarczyk wrote: > Hi everyone, > > I have some question considering about oracle_fdw and safety of use it in > production environment. > > Problem we would like to resolve using this extension is to move data from > five > Oracle tables to Postgres database. To avoid situation that we have to trasfer > data using sftp/ssh or another file based approach we decided to install > oracle_fdw. > > Our administrator have some doubt about this extension. He asked us, are we > sure that this extension safe enough and didn't make any kernel panic > sytuation > beacuse as all another extension, this one can operate on system kernel > level. > > Therefore if it's possible can You tell me more about safety this extension > and > release procedure and community of this module? Uh, oracle_fwd uses a kernel module? Are you sure? That is surprising. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: oracle_fwd - is it safe or not?
Hi, We have use it to "archive" 2 Oracle databases (8i and 9i) to pg 9.5 on windows (for a target of more than 250GB). We also use it to monitor our Oracle 11g databases, storing some performances / capacity planning data (like ASH, AWR, ...) in Postgres. In all cases, developer support was premium. Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: oracle_fwd - is it safe or not?
On 1/31/19 12:48 PM, legrand legrand wrote: Hi, We have use it to "archive" 2 Oracle databases (8i and 9i) to pg 9.5 on windows (for a target of more than 250GB). We also use it to monitor our Oracle 11g databases, storing some performances / capacity planning data (like ASH, AWR, ...) in Postgres. In all cases, developer support was premium. Regards PAscal Can you please expand on "support was premium". I'm not sure if that was the level of support purchased, or perhaps an indication that support was heavily used. Or have I missed it entirely?
Date calculation
Hi, v9.6.6 Is there a built in function to calculate, for example, next Sunday? For example, postgres=# select current_date, next_dow(current_date, 'Sunday'); date | date | 2019-01-31 | 2019-02-03 (1 row) Thanks -- Angular momentum makes the world go 'round.
Re: Date calculation
On Thu, Jan 31, 2019 at 02:11:14PM -0600, Ron wrote:
> Hi,
>
> v9.6.6
>
> Is there a built in function to calculate, for example, next Sunday?
>
> For example,
>
> postgres=# select current_date, next_dow(current_date, 'Sunday');
> date | date
> |
> 2019-01-31 | 2019-02-03
> (1 row)
Uh, this worked:
SELECT date_trunc('week', CURRENT_TIMESTAMP) + '6 days';
?column?
2019-02-03 00:00:00-05
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
Re: oracle_fwd - is it safe or not?
lup wrote > On 1/31/19 12:48 PM, legrand legrand wrote: >> >> In all cases, developer support was premium. >> > Can you please expand on "support was premium". I'm not sure if that > was the level of support purchased, or perhaps an indication that > support was heavily used. Or have I missed it entirely? Sorry for not being clear, we had many questions (and one bug) all answers (and one fix) where always fast and high level quality Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Date calculation
On 1/31/19 2:15 PM, Bruce Momjian wrote:
On Thu, Jan 31, 2019 at 02:11:14PM -0600, Ron wrote:
Hi,
v9.6.6
Is there a built in function to calculate, for example, next Sunday?
For example,
postgres=# select current_date, next_dow(current_date, 'Sunday');
date | date
|
2019-01-31 | 2019-02-03
(1 row)
Uh, this worked:
SELECT date_trunc('week', CURRENT_TIMESTAMP) + '6 days';
?column?
2019-02-03 00:00:00-05
Perfect! All I had to do was cast that as DATE...
Thanks
--
Angular momentum makes the world go 'round.
Re: Date calculation
On Thu, Jan 31, 2019 at 02:21:52PM -0600, Ron wrote:
> On 1/31/19 2:15 PM, Bruce Momjian wrote:
> >On Thu, Jan 31, 2019 at 02:11:14PM -0600, Ron wrote:
> >>Hi,
> >>
> >>v9.6.6
> >>
> >>Is there a built in function to calculate, for example, next Sunday?
> >>
> >>For example,
> >>
> >>postgres=# select current_date, next_dow(current_date, 'Sunday');
> >> date | date
> >>|
> >> 2019-01-31 | 2019-02-03
> >>(1 row)
> >Uh, this worked:
> >
> > SELECT date_trunc('week', CURRENT_TIMESTAMP) + '6 days';
> > ?column?
> >
> > 2019-02-03 00:00:00-05
>
> Perfect! All I had to do was cast that as DATE...
Oh, right, you want date, so use:
SELECT date_trunc('week', CURRENT_DATE) + '6 days';
?column?
2019-02-03 00:00:00-05
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
Re: Date calculation
> "Ron" == Ron writes:
Ron> Hi,
Ron> v9.6.6
Ron> Is there a built in function to calculate, for example, next
Ron> Sunday?
No, but such things aren't hard to calculate using the available
primitives.
To get "next Xday", for example, you can add 7 days and then do
"previous or current Xday". In turn, "previous or current Xday" can be
done by subtracting (X-Monday), doing date_trunc 'week', and adding
(X-Monday) again.
select current_date,
date_trunc('week', (current_date + 7 - 6)::timestamp)::date + 6;
current_date | ?column?
--+
2019-01-31 | 2019-02-03
(1 row)
If you do this sort of thing a lot, then define your own functions for
it:
-- create this to override the cast to timestamptz that otherwise messes
-- things up:
create function date_trunc(text,date)
returns date language sql immutable
as $f$
select date_trunc($1, $2::timestamp)::date;
$f$;
-- perfect hash function for weekday names, with Monday=0
-- (accepts upper, lower or mixed case)
create function dayno(text)
returns integer
language sql immutable
as $f$
select (( ((ascii(substring($1 from 3)) & 22)*10)
# (ascii($1) & 23) )*5 + 2) % 7;
$f$;
create function next_dow(start_date date, day_name text)
returns date language sql immutable
as $f$
select date_trunc('week', (start_date + 7 - dayno(day_name)))
+ dayno(day_name);
$f$;
select current_date,
next_dow(current_date, 'Thursday'),
next_dow(current_date, 'Friday');
current_date | next_dow | next_dow
--++
2019-01-31 | 2019-02-07 | 2019-02-01
--
Andrew (irc:RhodiumToad)
Re: Date calculation
> "Bruce" == Bruce Momjian writes:
Bruce> Oh, right, you want date, so use:
Bruce> SELECT date_trunc('week', CURRENT_DATE) + '6 days';
Three major things wrong with this:
1. If you do this on Sunday, it gives you the current day not the _next_
Sunday.
2. If you try and do this for other days of the week it doesn't work at
all, instead giving you the specified day of the current week whether or
not it's before or after the current day.
3. It's letting PG cast the date to a timestamptz, which is inefficient,
possibly incorrect, and mutable; you want to force it to cast to
timestamp without timezone instead. (A good rule of thumb is that you
virtually never want to cast dates to timestamptz; the natural cast from
date is to timestamp _without_ timezone.)
--
Andrew (irc:RhodiumToad)
