Re: multiple tables got corrupted

[Vasu Madhineni]

I could see block read I/O errors in /var/log/syslog. if those error fixed
by OS team, will it require recovery.

Also can i use LIMIT and OFFSET to locate corrupted rows?

Thanks in advance

Regards,
Vasu Madhineni

On Wed, Sep 16, 2020, 01:58 Magnus Hagander  wrote:

> Try reading them "row by row" until it breaks. That is, SELECT * FROM ...
> LIMIT 1, then LIMIT 2 etc. For more efficiency use a binary search starting
> at what seems like a reasonable place looking at the size of the table vs
> the first failed block to make it faster, but the principle is the same.
> Once it fails, you've found a corrupt block...
>
> //Magnus
>
>
> On Tue, Sep 15, 2020 at 12:46 PM Vasu Madhineni 
> wrote:
>
>> Is it possible to identify which rows are corrupted in particular tables.
>>
>> On Tue, Sep 15, 2020 at 5:36 PM Magnus Hagander 
>> wrote:
>>
>>>
>>>
>>> On Tue, Sep 15, 2020 at 11:15 AM Vasu Madhineni 
>>> wrote:
>>>
 Hi All,

 In one of my postgres databases multiple tables got corrupted and
 followed the below steps but still the same error.

 1.SET zero_damaged_pages = on
 2. VACUUM ANALYZE, VACUUM FULL
 but still same error.

>>>
>>>
>>> That is a very destructive first attempt. I hope you took a full
>>> disk-level backup of the database before you did that, as it can ruin your
>>> chances for forensics and data recovery for other issues.
>>>
>>>
>>> moh_fa=# VACUUM FULL;
 ERROR:  could not read block 9350 in file "base/1156523/1270812":
 Input/output error

 Tried to take backup of tables with pg_dump but same error. files exist
 physically in base location.

 How to proceed on this, no backup to restore.


>>> This is clearly some sort of disk error, and with no backups to restore
>>> you will definitely be losing data.
>>>
>>> I'd start by figuring out which tables have no corruption and do work,
>>> and back those up (with pg_dump for example) as soon as possible to a
>>> different machine -- since it's not exactly unlikely that further disk
>>> errors will appear.
>>>
>>> Once you've done that, identify the tables, and then try to do partial
>>> recovery. For example, if you look at the file 1270812, how big it is?
>>> PostgreSQL is failing to read block 9350 which is 76595200 bytes into the
>>> file. If this is at the very end of the file, you can for example try to
>>> get the data out until that point with LIMIT. If it's in the middle of the
>>> file, it gets more ticky, but similar approaches can be done.
>>>
>>> Also, unless you are running with data checksums enabled, I wouldn't
>>> fully trust the data in the tables that you *can* read either. Since you
>>> clearly have disk issues, they may have caused corruption elsewhere as
>>> well, so whatever verification you can do against other tables, you should
>>> do as well.
>>>
>>>
>>> You'll of course also want to check any kernel logs or storage system
>>> logs to see if they can give you a hint as to what happened, but they are
>>> unlikely to actually give you something that will help you fix the problem.
>>>
>>>

Obvious data mismatch in View2 which basically SELECT * from View1

[Ben]

Dear list,

Recently I am getting feedback, data in my analytic report is not 
repeatable. From time to time they get different data for the same time 
span.
(but IIRC previously it was OK). Therefore I started debuging the View 
chain for that report, during which I bumped into this issue/phenomenon.


In a over -simplified version:

CREATE VIEW2 AS SELECT * FROM VIEW1;
SELECT  col1 FROM VIEW2 WHERE cond1=True;
SELECT  col1 FROM VIEW1 WHERE cond1=True;

Now col1 from both views looks different. I don't know where to start to 
solve this problem.


The actual situation is a bit more than that, the following is the 
actual query:



    -- trying to audit utlog weighed stat
    with t as (
    select '2020-07-01 00:00:00'::timestamp t0, '2020--07-02 
0:0:0'::timestamp t1

    )
    --select * from t;
    select *
    -- from utlog.cache_stats_per_shift_per_reason_weighed_stats
    -- from utlog.stats_per_shift_filtered_per_reason
    from utlog.stats_per_shift_filtered (let's call 
it #View2 for short)
    -- from utlog.stats_per_shift_filtered_b0206  (let's call it 
#View1 for short)

    -- from utlog.stats_per_shift
    cross join t
    where wline = 'F02'  and wts >= t.t0 and wts < t.t1 and wsft ='D'
    limit 100
    ;

The Result for #View2

    wts | wsft | wspan  | wstate | wline | rcodes
    +--+++---+---
    2020-07-01 08:00:00 | D    |  0 | S00    | F02   | {PDCB}
    2020-07-01 09:50:01 | D    | 12.533 | S00    | F02   | {PDCB}
    2020-07-01 11:35:46 | D    | 12.217 | S00    | F02   | {CDSO}
    2020-07-01 13:22:58 | D    |   5.15 | S00    | F02   | {PDCB}
    2020-07-01 14:57:38 | D    |    6.8 | S00    | F02   | {PDCB}

    INDEX | COLUMN_NAME | DATA_TYPE
    --+-+
    1 | wts | timestamptz
    3 | wsft    | varchar
    4 | wspan   | float8
    5 | wstate  | varchar
    6 | wline   | varchar
    7 | rcodes  | text[]


Same query, the Result for #View1

    wts | wsft | wspan | wstate | wline | rcodes
    +--+---++---+---
    2020-07-01 08:00:00 | D    | 5 | S00    | F02   | {PDCB}
    2020-07-01 09:50:01 | D    |    13 | S00    | F02   | {PDCB}
    2020-07-01 11:35:46 | D    |    12 | S00    | F02   | {CDSO}
    2020-07-01 13:22:58 | D    | 5 | S00    | F02   | {PDCB}
    2020-07-01 14:57:38 | D    | 7 | S00    | F02   | {PDCB}

    INDEX | COLUMN_NAME | DATA_TYPE
    --+-+
    1 | wts | timestamptz
    3 | wsft    | varchar
    4 | wspan   | float8
    5 | wstate  | varchar
    6 | wline   | varchar
    7 | rcodes  | varchar[]

Reuslts in `wspan` column is inaccurate while both type are float8. Most 
weird thing is the 5 to 0 change. for Row 1.


The `_b0206`(#View1) is just a version of 
`stats_per_shift_filtered`(#View2) from past revisions.
I am sure the original CREATE statement for (#View2) is `CREATE VIEW ... 
AS SELECT * FROM _b0206`


Definition of View2 in SQLWorkbench/J generated schema:


    CREATE OR REPLACE VIEW utlog.stats_per_shift_filtered (#View2)
    (
    wts,
    wdate,
    wsft,
    wspan,
    wstate,
    wline,
    rcodes
    )
    AS
    SELECT stats_per_shift_filtered_u0206.wts,
    stats_per_shift_filtered_u0206.wsft::character varying AS wsft,
    stats_per_shift_filtered_u0206.wspan,
    stats_per_shift_filtered_u0206.wstate,
    stats_per_shift_filtered_u0206.wline,
    stats_per_shift_filtered_u0206.rcodes
    FROM utlog.stats_per_shift_filtered_u0206;  (as #View1 in this post)


It feels like the utlog.stats_per_shift_filtered_u0206 in 
utlog.stats_per_shift_filtered definition is a different object from 
utlog.stats_per_shift_filtered_u0206?


I am totally out of clues. Any help would be appreciated. Thanks.


Regards,

Ben

Re: Obvious data mismatch in View2 which basically SELECT * from View1

[Ron]

On 9/15/20 10:40 PM, Ben wrote:

Dear list,

Recently I am getting feedback, data in my analytic report is not 
repeatable. From time to time they get different data for the same time span.
(but IIRC previously it was OK). Therefore I started debuging the View 
chain for that report, during which I bumped into this issue/phenomenon.


In a over -simplified version:

CREATE VIEW2 AS SELECT * FROM VIEW1;
SELECT  col1 FROM VIEW2 WHERE cond1=True;
SELECT  col1 FROM VIEW1 WHERE cond1=True;

Now col1 from both views looks different. I don't know where to start to 
solve this problem.


The actual situation is a bit more than that, the following is the actual 
query:



    -- trying to audit utlog weighed stat
    with t as (
    select '2020-07-01 00:00:00'::timestamp t0, '2020--07-02 
0:0:0'::timestamp t1

    )
    --select * from t;
    select *
    -- from utlog.cache_stats_per_shift_per_reason_weighed_stats
    -- from utlog.stats_per_shift_filtered_per_reason
    from utlog.stats_per_shift_filtered (let's call it 
#View2 for short)
    -- from utlog.stats_per_shift_filtered_b0206  (let's call it 
#View1 for short)

    -- from utlog.stats_per_shift
    cross join t
    where wline = 'F02'  and wts >= t.t0 and wts < t.t1 and wsft ='D'
    limit 100
    ;

The Result for #View2

    wts | wsft | wspan  | wstate | wline | rcodes
    +--+++---+---
    2020-07-01 08:00:00 | D    |  0 | S00    | F02   | {PDCB}
    2020-07-01 09:50:01 | D    | 12.533 | S00    | F02   | {PDCB}
    2020-07-01 11:35:46 | D    | 12.217 | S00    | F02   | {CDSO}
    2020-07-01 13:22:58 | D    |   5.15 | S00    | F02   | {PDCB}
    2020-07-01 14:57:38 | D    |    6.8 | S00    | F02   | {PDCB}

    INDEX | COLUMN_NAME | DATA_TYPE
    --+-+
    1 | wts | timestamptz
    3 | wsft    | varchar
    4 | wspan   | float8
    5 | wstate  | varchar
    6 | wline   | varchar
    7 | rcodes  | text[]


Same query, the Result for #View1

    wts | wsft | wspan | wstate | wline | rcodes
    +--+---++---+---
    2020-07-01 08:00:00 | D    | 5 | S00    | F02   | {PDCB}
    2020-07-01 09:50:01 | D    |    13 | S00    | F02   | {PDCB}
    2020-07-01 11:35:46 | D    |    12 | S00    | F02   | {CDSO}
    2020-07-01 13:22:58 | D    | 5 | S00    | F02   | {PDCB}
    2020-07-01 14:57:38 | D    | 7 | S00    | F02   | {PDCB}

    INDEX | COLUMN_NAME | DATA_TYPE
    --+-+
    1 | wts | timestamptz
    3 | wsft    | varchar
    4 | wspan   | float8
    5 | wstate  | varchar
    6 | wline   | varchar
    7 | rcodes  | varchar[]

Reuslts in `wspan` column is inaccurate while both type are float8. Most 
weird thing is the 5 to 0 change. for Row 1.


The `_b0206`(#View1) is just a version of 
`stats_per_shift_filtered`(#View2) from past revisions.
I am sure the original CREATE statement for (#View2) is `CREATE VIEW ... 
AS SELECT * FROM _b0206`


Definition of View2 in SQLWorkbench/J generated schema:


    CREATE OR REPLACE VIEW utlog.stats_per_shift_filtered (#View2)
    (
    wts,
    wdate,
    wsft,
    wspan,
    wstate,
    wline,
    rcodes
    )
    AS
    SELECT stats_per_shift_filtered_u0206.wts,
    stats_per_shift_filtered_u0206.wsft::character varying AS wsft,
    stats_per_shift_filtered_u0206.wspan,
    stats_per_shift_filtered_u0206.wstate,
    stats_per_shift_filtered_u0206.wline,
    stats_per_shift_filtered_u0206.rcodes
    FROM utlog.stats_per_shift_filtered_u0206;  (as #View1 in this post)


It feels like the utlog.stats_per_shift_filtered_u0206 in 
utlog.stats_per_shift_filtered definition is a different object from 
utlog.stats_per_shift_filtered_u0206?


I am totally out of clues. Any help would be appreciated. Thanks.


Try the queries in a serializable read only transaction.  That should any 
possible changes in the underlying data.


START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
SELECT  col1 FROM VIEW2 WHERE cond1=True;
SELECT  col1 FROM VIEW1 WHERE cond1=True;
COMMIT;

--
Angular momentum makes the world go 'round.

Re: Obvious data mismatch in View2 which basically SELECT * from View1

[Magnus Hagander]

On Wed, Sep 16, 2020 at 9:26 AM Ben  wrote:

> Dear list,
>
> Recently I am getting feedback, data in my analytic report is not
> repeatable. From time to time they get different data for the same time
> span.
> (but IIRC previously it was OK). Therefore I started debuging the View
> chain for that report, during which I bumped into this issue/phenomenon.
>
> In a over -simplified version:
>
> CREATE VIEW2 AS SELECT * FROM VIEW1;
> SELECT  col1 FROM VIEW2 WHERE cond1=True;
> SELECT  col1 FROM VIEW1 WHERE cond1=True;
>
> Now col1 from both views looks different. I don't know where to start to
> solve this problem.
>
> The actual situation is a bit more than that, the following is the
> actual query:
>
>
>  -- trying to audit utlog weighed stat
>  with t as (
>  select '2020-07-01 00:00:00'::timestamp t0, '2020--07-02
> 0:0:0'::timestamp t1
>  )
>  --select * from t;
>  select *
>  -- from utlog.cache_stats_per_shift_per_reason_weighed_stats
>  -- from utlog.stats_per_shift_filtered_per_reason
>  from utlog.stats_per_shift_filtered (let's call
> it #View2 for short)
>  -- from utlog.stats_per_shift_filtered_b0206  (let's call it
> #View1 for short)
>  -- from utlog.stats_per_shift
>  cross join t
>  where wline = 'F02'  and wts >= t.t0 and wts < t.t1 and wsft ='D'
>  limit 100
>  ;
>


Not sure if it might be something lost in your simplification here, but you
have a LIMIT with no ORDER BY there. That basically means "give me 100
random rows" (but not with a very good random level). It does not return
rows in a consistent/predictable order. So as long as that query is part of
what you're doing, you should not be surprised if you get the rows in an
inconsistent/unpredictable order, with whatever follow-on effects that
might have. (And it can lead to weird follow-on effects like the ones
you're talking about when used in larger query structures)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Problems with MemoryContextSwitchTo ()

[Yessica Brinkmann]

 Good Morning.
I will greatly appreciate a help please on this subject. I actually did
several tests already and tried a lot to fix it myself. But I am not able
to. And I really need to know this in order to finish my university thesis. If
someone can help me please.
Best regards,
Yessica Brinkmann


El mié., 16 sept. 2020 a las 0:42, Yessica Brinkmann (<
brinkmann.yess...@gmail.com>) escribió:

>
>
> Hello.
> I think several of you will already remember me. I'm the one with the
> IndexAdviser topic. Only that I changed my email address.
> As you may recall, I am doing my thesis on the subject of IndexAdviser
> modifications.
> I really appreciate the help they have given me in various Postgresql
> groups.
> Well, I was really nearing the end of the programming part of the thesis,
> when I had a problem to be able to compile my program in a moment, and by
> accident some lines of source code were moved.
> And for this reason, I think I have problems again with the context switch
> issue, since at some point my context switch stopped working for me, I
> think because of the issue that some lines of source code were moved.
> Well, the fact is that I have a function called get_columnnames, which in
> the second foreach, is printing the values of idxcd-> varattnnames [i] the
> null value.
> This second foreach, I only do it to test if the data is really saved well
> and if I can recover it properly.
> And since the data is not retrieved properly, or is not saved properly, in
> the following functions of my program, the value of idxcd-> varattnnames
> [i] continues to appear as null.
> I will appreciate a lot please help, if you can tell me please why the
> function prints null in the values of idxcd-> varattnnames [i], in the
> second foreach, if it is due to an error in the context switch, or why it
> could be .
>
> I send my function as an attachment.
>
>
> Best regards,
> Yessica Brinkmann.
>

Re: Problems with MemoryContextSwitchTo ()

[Pavel Stehule]

st 16. 9. 2020 v 13:32 odesílatel Yessica Brinkmann <
brinkmann.yess...@gmail.com> napsal:

> Good Morning.
> I will greatly appreciate a help please on this subject. I actually did
> several tests already and tried a lot to fix it myself. But I am not able
> to. And I really need to know this in order to finish my university
> thesis. If someone can help me please.
> Best regards,
> Yessica Brinkmann
>

please, can you attach your code, and can you show the error message?

It is hard to say what is the problem from your mail?

Regards

Pavel



>
> El mié., 16 sept. 2020 a las 0:42, Yessica Brinkmann (<
> brinkmann.yess...@gmail.com>) escribió:
>
>>
>>
>> Hello.
>> I think several of you will already remember me. I'm the one with the
>> IndexAdviser topic. Only that I changed my email address.
>> As you may recall, I am doing my thesis on the subject of IndexAdviser
>> modifications.
>> I really appreciate the help they have given me in various Postgresql
>> groups.
>> Well, I was really nearing the end of the programming part of the thesis,
>> when I had a problem to be able to compile my program in a moment, and by
>> accident some lines of source code were moved.
>> And for this reason, I think I have problems again with the context
>> switch issue, since at some point my context switch stopped working for me,
>> I think because of the issue that some lines of source code were moved.
>> Well, the fact is that I have a function called get_columnnames, which in
>> the second foreach, is printing the values of idxcd-> varattnnames [i] the
>> null value.
>> This second foreach, I only do it to test if the data is really saved
>> well and if I can recover it properly.
>> And since the data is not retrieved properly, or is not saved properly,
>> in the following functions of my program, the value of idxcd-> varattnnames
>> [i] continues to appear as null.
>> I will appreciate a lot please help, if you can tell me please why the
>> function prints null in the values of idxcd-> varattnnames [i], in the
>> second foreach, if it is due to an error in the context switch, or why it
>> could be .
>>
>> I send my function as an attachment.
>>
>>
>> Best regards,
>> Yessica Brinkmann.
>>
>

Re: Obvious data mismatch in View2 which basically SELECT * from View1

[Ben]

Hi Magnus,

Thanks for the heads up.

For the data posted in this email, the listed result is the full result 
set for that query.

I have checked the data in view2 and view1 with various conditions.
Both view has many difference in column wspan::float8.
The condition in the shown query is intended to show just a small set of 
them.

But you are right, I should be more cautious. Thanks for the headsup.

Regards,
Ben


On 9/16/20 3:35 PM, Magnus Hagander wrote:



On Wed, Sep 16, 2020 at 9:26 AM Ben > wrote:


Dear list,

Recently I am getting feedback, data in my analytic report is not
repeatable. From time to time they get different data for the same
time
span.
(but IIRC previously it was OK). Therefore I started debuging the
View
chain for that report, during which I bumped into this
issue/phenomenon.

In a over -simplified version:

CREATE VIEW2 AS SELECT * FROM VIEW1;
SELECT  col1 FROM VIEW2 WHERE cond1=True;
SELECT  col1 FROM VIEW1 WHERE cond1=True;

Now col1 from both views looks different. I don't know where to
start to
solve this problem.

The actual situation is a bit more than that, the following is the
actual query:


 -- trying to audit utlog weighed stat
 with t as (
 select '2020-07-01 00:00:00'::timestamp t0, '2020--07-02
0:0:0'::timestamp t1
 )
 --select * from t;
 select *
 -- from utlog.cache_stats_per_shift_per_reason_weighed_stats
 -- from utlog.stats_per_shift_filtered_per_reason
 from utlog.stats_per_shift_filtered (let's call
it #View2 for short)
 -- from utlog.stats_per_shift_filtered_b0206 (let's call it
#View1 for short)
 -- from utlog.stats_per_shift
 cross join t
 where wline = 'F02'  and wts >= t.t0 and wts < t.t1 and wsft ='D'
 limit 100
 ;



Not sure if it might be something lost in your simplification here, 
but you have a LIMIT with no ORDER BY there. That basically means 
"give me 100 random rows" (but not with a very good random level). It 
does not return rows in a consistent/predictable order. So as long as 
that query is part of what you're doing, you should not be surprised 
if you get the rows in an inconsistent/unpredictable order, with 
whatever follow-on effects that might have. (And it can lead to weird 
follow-on effects like the ones you're talking about when used in 
larger query structures)


--
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Problems with MemoryContextSwitchTo ()

[Yessica Brinkmann]

Good Morning.I will greatly appreciate a help please on this subject.I actually did several tests already and tried a lot to fix it myself. But I am not able to. And I really need to know this in order to finish my university thesis. If someone can help me please.Best regards,Yessica Brinkmann Mensaje original Asunto: Problems with MemoryContextSwitchTo ()De: Yessica Brinkmann Para: pgsql-general@lists.postgresql.orgCC: 

Hello.
I think several of you will already remember me. I'm the one with the IndexAdviser topic. Only that I changed my email address.
As you may recall, I am doing my thesis on the subject of IndexAdviser modifications.
I really appreciate the help they have given me in various Postgresql groups.
Well, I was really nearing the end of the programming part of the 
thesis, when I had a problem to be able to compile my program in a 
moment, and by accident some lines of source code were moved.
And for this reason, I think I have problems again with the context 
switch issue, since at some point my context switch stopped working for 
me, I think because of the issue that some lines of source code were 
moved.
Well, the fact is that I have a function called get_columnnames, which 
in the second foreach, is printing the values of idxcd-> 
varattnnames [i] the null value.
This second foreach, I only do it to test if the data is really saved well and if I can recover it properly.
And since the data is not retrieved properly, or is not saved properly, 
in the following functions of my program, the value of idxcd-> 
varattnnames [i] continues to appear as null.
I will appreciate a lot please help, if you can tell me please why the 
function prints null in the values of idxcd-> varattnnames [i], in 
the second foreach, if it is due to an error in the context switch, or 
why it could be .
I send my function as an attachment.


Best regards,
Yessica Brinkmann.

Re: Problems with MemoryContextSwitchTo ()

[Yessica Brinkmann]

Well, the fact is that I have a function called get_columnnames, which in the second foreach, is printing the values ​​of idxcd-> varattnnames [i] the null value. This is the error.  Mensaje original Asunto: Re: Problems with MemoryContextSwitchTo ()De: Pavel Stehule Para: Yessica Brinkmann CC: pgsql-general st 16. 9. 2020 v 13:32 odesílatel Yessica Brinkmann  napsal:
Good Morning.I will greatly appreciate a help please on this subject. I actually did several tests already and tried a lot to fix it myself. But I am not able to. And I really need to know this in order to finish my university thesis. If someone can help me please.Best regards,Yessica Brinkmannplease, can you attach your code, and can you show the error message? It is hard to say what is the problem from your mail?RegardsPavel

El mié., 16 sept. 2020 a las 0:42, Yessica Brinkmann () escribió:

Hello.
I think several of you will already remember me. I'm the one with the IndexAdviser topic. Only that I changed my email address.
As you may recall, I am doing my thesis on the subject of IndexAdviser modifications.
I really appreciate the help they have given me in various Postgresql groups.
Well, I was really nearing the end of the programming part of the 
thesis, when I had a problem to be able to compile my program in a 
moment, and by accident some lines of source code were moved.
And for this reason, I think I have problems again with the context 
switch issue, since at some point my context switch stopped working for 
me, I think because of the issue that some lines of source code were 
moved.
Well, the fact is that I have a function called get_columnnames, which 
in the second foreach, is printing the values of idxcd-> 
varattnnames [i] the null value.
This second foreach, I only do it to test if the data is really saved well and if I can recover it properly.
And since the data is not retrieved properly, or is not saved properly, 
in the following functions of my program, the value of idxcd-> 
varattnnames [i] continues to appear as null.
I will appreciate a lot please help, if you can tell me please why the 
function prints null in the values of idxcd-> varattnnames [i], in 
the second foreach, if it is due to an error in the context switch, or 
why it could be .
I send my function as an attachment.


Best regards,
Yessica Brinkmann.






static List*
get_columnnames( List* candidates )
{
int proc;
int ret;
StringInfoData  query;  /* string for Query */
StringInfoData  cols;   /* string for Columns */
MemoryContext outerContext;
ListCell*cell;




IndexCandidate* idxcd;


elog( DEBUG3, "IND ADV: get_column_names: ENTER" );




initStringInfo( &query );
initStringInfo( &cols );

foreach( cell, candidates ) /* foreach cell in candidates */
{

int i;



/*elog (INFO, "Ingresando a foreach");*/
idxcd = (IndexCandidate*)lfirst( cell );

if (idxcd == NULL) {
elog( INFO, "idxcd IS NULL" );
continue; /* Or is that fatal enough to break instead? */
}

if (!idxcd->idxused)
continue;




/* pfree() the memory allocated for the previous 
candidate. FIXME: Avoid
* meddling with the internals of a StringInfo, and try 
to use an API.
*/
if( cols.len > 0 )
{
initStringInfo(&cols);
} /*IF col.len>0*/

if( query.len > 0 )
{
initStringInfo(&query);
} /*IF col.len>0*/

elog(INFO,"reloid:%d", idxcd->reloid);
appendStringInfo( &query, "select a.attname from 
pg_class c,pg_attribute a where c.oid=%d AND a.attrelid = c.oid AND (", 
idxcd->reloid);

/*elog(INFO,"QUERY:%s", query.data);*/

/*elog(INFO,"ncols:%d", idxcd->ncols);*/

for (i = 0; i < idxcd->ncols; ++i)
{
/*elog(INFO,"i:%d", i);*/
/*elog(INFO,"var attno i:%d", 
idxcd->varattno[i]);*/
/*elog(INFO,"cols:%s", cols.data);*/
appendStringInfo( &cols, "%s a.attnum=%d", (i>0 
? " OR" : ""), idxcd->varattno[i]);
/*

Re: Problems with MemoryContextSwitchTo ()

[Pavel Stehule]

st 16. 9. 2020 v 15:09 odesílatel Yessica Brinkmann <
brinkmann.yess...@gmail.com> napsal:

> Well, the fact is that I have a function called get_columnnames, which in
> the second foreach, is printing the values of idxcd-> varattnnames [i] the
> null value. This is the error.


How you get this list? It is result of SPI_exec*** ?

There is SPI_finish(), that cleaning a memory used by SPI interface, and
theoretically it can clean result of previous query.

Regards

Pavel


>
>  Mensaje original 
> Asunto: Re: Problems with MemoryContextSwitchTo ()
> De: Pavel Stehule
> Para: Yessica Brinkmann
> CC: pgsql-general
>
>
>
>
> st 16. 9. 2020 v 13:32 odesílatel Yessica Brinkmann <
> brinkmann.yess...@gmail.com> napsal:
>
>> Good Morning.
>> I will greatly appreciate a help please on this subject. I actually did
>> several tests already and tried a lot to fix it myself. But I am not
>> able to. And I really need to know this in order to finish my university
>> thesis. If someone can help me please.
>> Best regards,
>> Yessica Brinkmann
>>
>
> please, can you attach your code, and can you show the error message?
>
> It is hard to say what is the problem from your mail?
>
> Regards
>
> Pavel
>
>
>
>>
>> El mié., 16 sept. 2020 a las 0:42, Yessica Brinkmann (<
>> brinkmann.yess...@gmail.com>) escribió:
>>
>>>
>>>
>>> Hello.
>>> I think several of you will already remember me. I'm the one with the
>>> IndexAdviser topic. Only that I changed my email address.
>>> As you may recall, I am doing my thesis on the subject of IndexAdviser
>>> modifications.
>>> I really appreciate the help they have given me in various Postgresql
>>> groups.
>>> Well, I was really nearing the end of the programming part of the
>>> thesis, when I had a problem to be able to compile my program in a moment,
>>> and by accident some lines of source code were moved.
>>> And for this reason, I think I have problems again with the context
>>> switch issue, since at some point my context switch stopped working for me,
>>> I think because of the issue that some lines of source code were moved.
>>> Well, the fact is that I have a function called get_columnnames, which
>>> in the second foreach, is printing the values of idxcd-> varattnnames [i]
>>> the null value.
>>> This second foreach, I only do it to test if the data is really saved
>>> well and if I can recover it properly.
>>> And since the data is not retrieved properly, or is not saved properly,
>>> in the following functions of my program, the value of idxcd-> varattnnames
>>> [i] continues to appear as null.
>>> I will appreciate a lot please help, if you can tell me please why the
>>> function prints null in the values of idxcd-> varattnnames [i], in the
>>> second foreach, if it is due to an error in the context switch, or why it
>>> could be .
>>>
>>> I send my function as an attachment.
>>>
>>>
>>> Best regards,
>>> Yessica Brinkmann.
>>>
>>

Re: Problems with MemoryContextSwitchTo ()

[Yessica Brinkmann]

 Good night,
Thank you very much for the answer.
Excuse me please just answered, I was at my job.
And yes, I get the results of the list with SPI_exec.
And I am also using SPI_finish (). But I save the results of the previous
query in the array idxcd-> varattnnames.
And in the second foreach it should print me the values that I got in the
query, since I print the values of idxcd-> varattnnames, but it is printing
null.
And I think the memory context switch is fine too, so I really don't know
why it prints null to me.
I will greatly appreciate a help please. I can't really understand why it
prints null in the second foreach, even though I already tried various
changes.
Best regards,
Yessica Brinkmann

El mié., 16 sept. 2020 a las 9:16, Pavel Stehule ()
escribió:

>
>
> st 16. 9. 2020 v 15:09 odesílatel Yessica Brinkmann <
> brinkmann.yess...@gmail.com> napsal:
>
>> Well, the fact is that I have a function called get_columnnames, which in
>> the second foreach, is printing the values of idxcd-> varattnnames [i] the
>> null value. This is the error.
>
>
> How you get this list? It is result of SPI_exec*** ?
>
> There is SPI_finish(), that cleaning a memory used by SPI interface, and
> theoretically it can clean result of previous query.
>
> Regards
>
> Pavel
>
>
>>
>>  Mensaje original 
>> Asunto: Re: Problems with MemoryContextSwitchTo ()
>> De: Pavel Stehule
>> Para: Yessica Brinkmann
>> CC: pgsql-general
>>
>>
>>
>>
>> st 16. 9. 2020 v 13:32 odesílatel Yessica Brinkmann <
>> brinkmann.yess...@gmail.com> napsal:
>>
>>> Good Morning.
>>> I will greatly appreciate a help please on this subject. I actually did
>>> several tests already and tried a lot to fix it myself. But I am not
>>> able to. And I really need to know this in order to finish my
>>> university thesis. If someone can help me please.
>>> Best regards,
>>> Yessica Brinkmann
>>>
>>
>> please, can you attach your code, and can you show the error message?
>>
>> It is hard to say what is the problem from your mail?
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>>
>>> El mié., 16 sept. 2020 a las 0:42, Yessica Brinkmann (<
>>> brinkmann.yess...@gmail.com>) escribió:
>>>


 Hello.
 I think several of you will already remember me. I'm the one with the
 IndexAdviser topic. Only that I changed my email address.
 As you may recall, I am doing my thesis on the subject of IndexAdviser
 modifications.
 I really appreciate the help they have given me in various Postgresql
 groups.
 Well, I was really nearing the end of the programming part of the
 thesis, when I had a problem to be able to compile my program in a moment,
 and by accident some lines of source code were moved.
 And for this reason, I think I have problems again with the context
 switch issue, since at some point my context switch stopped working for me,
 I think because of the issue that some lines of source code were moved.
 Well, the fact is that I have a function called get_columnnames, which
 in the second foreach, is printing the values of idxcd-> varattnnames [i]
 the null value.
 This second foreach, I only do it to test if the data is really saved
 well and if I can recover it properly.
 And since the data is not retrieved properly, or is not saved properly,
 in the following functions of my program, the value of idxcd-> varattnnames
 [i] continues to appear as null.
 I will appreciate a lot please help, if you can tell me please why the
 function prints null in the values of idxcd-> varattnnames [i], in the
 second foreach, if it is due to an error in the context switch, or why it
 could be .

 I send my function as an attachment.


 Best regards,
 Yessica Brinkmann.

>>>

Re: Problems with MemoryContextSwitchTo ()

[Pavel Stehule]

čt 17. 9. 2020 v 3:16 odesílatel Yessica Brinkmann <
brinkmann.yess...@gmail.com> napsal:

> Good night,
> Thank you very much for the answer.
> Excuse me please just answered, I was at my job.
> And yes, I get the results of the list with SPI_exec.
> And I am also using SPI_finish (). But I save the results of the previous
> query in the array idxcd-> varattnnames.
> And in the second foreach it should print me the values that I got in the
> query, since I print the values of idxcd-> varattnnames, but it is printing
> null.
> And I think the memory context switch is fine too, so I really don't know
> why it prints null to me.
> I will greatly appreciate a help please. I can't really understand why it
> prints null in the second foreach, even though I already tried various
> changes.
>

do you use Postgres with active assertions ?

postgres=# show debug_assertions ;
┌──┐
│ debug_assertions │
╞══╡
│ on   │
└──┘
(1 row)

maybe you somewhere allocate less memory then it is necessary and some
memset rewrites memory with your data. When you have a problem with memory,
then you should to very carefully check a) if memory is allocated in good
context, b) if memory was allocated enough (sometimes I just allocated x +
10 for testing).

Can you write a reproducer ? code that I can check at my home. Postgres is
pretty complex system, and it is hard to fix some issue without access to
code








Best regards,
> Yessica Brinkmann
>
> El mié., 16 sept. 2020 a las 9:16, Pavel Stehule ()
> escribió:
>
>>
>>
>> st 16. 9. 2020 v 15:09 odesílatel Yessica Brinkmann <
>> brinkmann.yess...@gmail.com> napsal:
>>
>>> Well, the fact is that I have a function called get_columnnames, which
>>> in the second foreach, is printing the values of idxcd-> varattnnames [i]
>>> the null value. This is the error.
>>
>>
>> How you get this list? It is result of SPI_exec*** ?
>>
>> There is SPI_finish(), that cleaning a memory used by SPI interface, and
>> theoretically it can clean result of previous query.
>>
>> Regards
>>
>> Pavel
>>
>>
>>>
>>>  Mensaje original 
>>> Asunto: Re: Problems with MemoryContextSwitchTo ()
>>> De: Pavel Stehule
>>> Para: Yessica Brinkmann
>>> CC: pgsql-general
>>>
>>>
>>>
>>>
>>> st 16. 9. 2020 v 13:32 odesílatel Yessica Brinkmann <
>>> brinkmann.yess...@gmail.com> napsal:
>>>
 Good Morning.
 I will greatly appreciate a help please on this subject. I actually
 did several tests already and tried a lot to fix it myself. But I am
 not able to. And I really need to know this in order to finish my
 university thesis. If someone can help me please.
 Best regards,
 Yessica Brinkmann

>>>
>>> please, can you attach your code, and can you show the error message?
>>>
>>> It is hard to say what is the problem from your mail?
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
>>>

 El mié., 16 sept. 2020 a las 0:42, Yessica Brinkmann (<
 brinkmann.yess...@gmail.com>) escribió:

>
>
> Hello.
> I think several of you will already remember me. I'm the one with the
> IndexAdviser topic. Only that I changed my email address.
> As you may recall, I am doing my thesis on the subject of IndexAdviser
> modifications.
> I really appreciate the help they have given me in various Postgresql
> groups.
> Well, I was really nearing the end of the programming part of the
> thesis, when I had a problem to be able to compile my program in a moment,
> and by accident some lines of source code were moved.
> And for this reason, I think I have problems again with the context
> switch issue, since at some point my context switch stopped working for 
> me,
> I think because of the issue that some lines of source code were moved.
> Well, the fact is that I have a function called get_columnnames, which
> in the second foreach, is printing the values of idxcd-> varattnnames [i]
> the null value.
> This second foreach, I only do it to test if the data is really saved
> well and if I can recover it properly.
> And since the data is not retrieved properly, or is not saved
> properly, in the following functions of my program, the value of idxcd->
> varattnnames [i] continues to appear as null.
> I will appreciate a lot please help, if you can tell me please why the
> function prints null in the values of idxcd-> varattnnames [i], in the
> second foreach, if it is due to an error in the context switch, or why it
> could be .
>
> I send my function as an attachment.
>
>
> Best regards,
> Yessica Brinkmann.
>

Re: Problems with MemoryContextSwitchTo ()

[Pavel Stehule]

čt 17. 9. 2020 v 6:09 odesílatel Pavel Stehule 
napsal:

>
>
> čt 17. 9. 2020 v 3:16 odesílatel Yessica Brinkmann <
> brinkmann.yess...@gmail.com> napsal:
>
>> Good night,
>> Thank you very much for the answer.
>> Excuse me please just answered, I was at my job.
>> And yes, I get the results of the list with SPI_exec.
>> And I am also using SPI_finish (). But I save the results of the
>> previous query in the array idxcd-> varattnnames.
>> And in the second foreach it should print me the values that I got in the
>> query, since I print the values of idxcd-> varattnnames, but it is printing
>> null.
>> And I think the memory context switch is fine too, so I really don't know
>> why it prints null to me.
>> I will greatly appreciate a help please. I can't really understand why
>> it prints null in the second foreach, even though I already tried various
>> changes.
>>
>
> do you use Postgres with active assertions ?
>
> postgres=# show debug_assertions ;
> ┌──┐
> │ debug_assertions │
> ╞══╡
> │ on   │
> └──┘
> (1 row)
>
> maybe you somewhere allocate less memory then it is necessary and some
> memset rewrites memory with your data. When you have a problem with memory,
> then you should to very carefully check a) if memory is allocated in good
> context, b) if memory was allocated enough (sometimes I just allocated x +
> 10 for testing).
>
> Can you write a reproducer ? code that I can check at my home. Postgres is
> pretty complex system, and it is hard to fix some issue without access to
> code
>
>
This is some messy in your code

MemoryContext oldContext = MemoryContextSwitchTo( outerContext );
MemoryContextSwitchTo( oldContext );

Unfortunately, your code is not well structured - it is hard to read it.
One hint - if you find an issue - try to reduce lines of your code which is
possible until you reproduce this issue or until you understand this issue.

The sequence of statements is a little bit obscure :)

initStringInfo( &query );

*/
if( cols.len > 0 )
{
initStringInfo(&cols);
} /*IF col.len>0*/

Instead

foreach( cell, candidates ) /* foreach cell in candidates */
{

int i;



   /*elog (INFO, "Ingresando a foreach");*/
idxcd = (IndexCandidate*)lfirst( cell );

if (idxcd == NULL) {
elog( INFO, "idxcd IS NULL" );
continue; /* Or is that fatal enough to break instead? */
}


you can write

idxcd = (IndexCandidate *) linitial(candidates);

The problem in your example is fact so it is a mix of copy/paste fragments.
I wrote similar code, and almost everyone I know (when I started). But it
is not possible to fix this code - start from scratch. The code must be
readable (every time). And when I write code inside some complex
environment (like Postgres is), I write code in very small fragments, in
very small steps, and every time I try to compile, restart and check the
result. Then I know what line, or what statement is the problem.

Regards

Pavel






>
>
>
>
>
>
> Best regards,
>> Yessica Brinkmann
>>
>> El mié., 16 sept. 2020 a las 9:16, Pavel Stehule (<
>> pavel.steh...@gmail.com>) escribió:
>>
>>>
>>>
>>> st 16. 9. 2020 v 15:09 odesílatel Yessica Brinkmann <
>>> brinkmann.yess...@gmail.com> napsal:
>>>
 Well, the fact is that I have a function called get_columnnames, which
 in the second foreach, is printing the values of idxcd-> varattnnames [i]
 the null value. This is the error.
>>>
>>>
>>> How you get this list? It is result of SPI_exec*** ?
>>>
>>> There is SPI_finish(), that cleaning a memory used by SPI interface, and
>>> theoretically it can clean result of previous query.
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>

  Mensaje original 
 Asunto: Re: Problems with MemoryContextSwitchTo ()
 De: Pavel Stehule
 Para: Yessica Brinkmann
 CC: pgsql-general




 st 16. 9. 2020 v 13:32 odesílatel Yessica Brinkmann <
 brinkmann.yess...@gmail.com> napsal:

> Good Morning.
> I will greatly appreciate a help please on this subject. I actually
> did several tests already and tried a lot to fix it myself. But I am
> not able to. And I really need to know this in order to finish my
> university thesis. If someone can help me please.
> Best regards,
> Yessica Brinkmann
>

 please, can you attach your code, and can you show the error message?

 It is hard to say what is the problem from your mail?

 Regards

 Pavel



>
> El mié., 16 sept. 2020 a las 0:42, Yessica Brinkmann (<
> brinkmann.yess...@gmail.com>) escribió:
>
>>
>>
>> Hello.
>> I think several of you will already remember me. I'm the one with the
>> IndexAdviser topic. Only that I changed my email address.
>> As you may recall, I am doing my thesis on the subject of
>> IndexAdviser modifications.
>> I really appreciate the help they have given me in various Postgre